User Profile
SergeiBaklan
Diamond Contributor
Joined 10 years ago
User Widgets
Recent Discussions
Re: Importing data into pivot excel from SSAS Tabular, wrong data type
That's good practice to ensure all fields data types are declared to final step in Power Query. Otherwise that could be issues like this plus you could slightly improve performance. And that's not a bug, that's by design - data model has no idea what do we assume using "any" data type, treats all such fields as texts.Re: Move columns with excel office script
The only, it moves values, not formulae. As variant function main(workbook: ExcelScript.Workbook) { const table = workbook.getTable("Table1") moveColumn( table, "Property Class", 0) moveColumn( table, "Property Description", 1) } const moveColumn = ( table: ExcelScript.Table, colName: string, index: number): void => { const column = table.getColumn(colName) if (!column || index >= table.getColumns().length ) { return } const n = index + +(index > table.getColumnByName("C").getIndex()) table.addColumn(n) const source = table.getColumnByName(colName) source.getRange().moveTo(table.getColumns()[n].getRange()) source.delete() }Re: Preventing duplicate selections
Let assume you have Company List sheet as and in each employee sheet (in the sample from A to C) data starts exactly from A5 With that we may generate spill for validation which has values from Company List not repeating in employee sheets =UNIQUE( VSTACK( TOCOL( $B$5:$B$70, 3), TOCOL( VSTACK('A:C'!$A$5:$A$70),3 ) ),,1) and use this spill in data validation as Please check in attached fileRe: Conditional Formatting Rules for Icon Sets
It's not clear what you'd like to receive. Here on the left "default" rule which applies green to the values for which (value - min) / (max - min) is greater than 66%, similar for next ones. On the right we apply green to the values which are greater than 10% of total sum, yellow to ones between 5% and 10% of total sum, red to the rest. Depends on goals you may generate another variants.Re: sumifs doesnt work because of power query
In addition what do you mean under "formula not working", it returns an error or result is not as you expected? If some result is returned, is it higher or lower of expected one? If in P5 is date, not day, right part of the SCRAP!$B:$B,"<="&DATE(YEAR(SCRAP!$B$2),MONTH(SCRAP!$B$2),P$5) returns date in next century and condition is always TRUE if in column B are dates. If in column B are texts which looks like dates, condition always returns FALSE since any text is "greater than" any number. Date in Excel is actually number.Re: Excel Not Refreshing Data from Power BI Semantic Model (Live Connection via XLMA)
IMHO, XMLA endpoint works with Premium capacity. Plus there are some limitations on semantic model. With Pro license works connection established as Data-> Get Data-> From Fabric & Power Platform -> From Power BI -> select semantic model -> Insert PivotTable (or Table).Re: STOCKHISTORY values don't match with actual data
It looks like in STOCKHISTORY we see adjusted price for above ticker CMIG4 - Cemig - Resultados, Dividendos, Cotação e Indicadores - Investidor10 Both Yahoo and Google Finance show standard price Don't know why so. That's question to Refinitiv, data provider for STOCKHISTORY.
