Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have a simple power query that sorts a column, and a step to determine when a value changes in the sorted column. This step that determines if the value changes seems to cause the query to hang (not respond).
When I click Close and Apply in Power BI, the data load starts but never seems to stop - it is stuck on this 'Apply query changes' dialog (I left it for an hour and cancelled it).
Here is the query (the underlined bit in the last line is what's causing it - if I remove it or if I do not refer to the previous value - the query is applied in less than 5 seconds)
Here is the source Excel file if you want to reproduce the issue: Source file
let
Source = Excel.Workbook(File.Contents("C:\temp\Revised risk raw data.xlsx"), null, true),
#"raw data_Sheet" = Source{[Item="raw data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"raw data_Sheet", [PromoteAllScalars=true]),
#"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"IncidentTimestamp"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"UDATE", type text}, {"UTIME", type text}, {"OBJID", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "IncidentTimestamp", each [OBJID] & [UDATE] & [UTIME]),
Sort = Table.Sort(#"Added Custom",{{"IncidentTimestamp", Order.Descending}}),
Buffer = Table.Buffer(Sort),
AddIndex = Table.AddIndexColumn(Buffer, "Index", 0, 1, Int64.Type),
AddFilterColumn = Table.AddColumn(AddIndex, "ChangeIndicator", each if [Index] = 0 then "New Value" else if [Obj and Value] = AddIndex{[Index]-1}[Obj and Value] then "No Change" else "New Value", type text )
in
AddFilterColumn
Solved! Go to Solution.
Hi
I managed to solve it with a workaround.
My requirement was to compare the current row with the previous row - so all I did was:
- remove the line that used the index (-1) to compare to the previous row
- adding a new column (index + 1)
- merge the current query with itself (on the index and index+1 columns)
Hi
I managed to solve it with a workaround.
My requirement was to compare the current row with the previous row - so all I did was:
- remove the line that used the index (-1) to compare to the previous row
- adding a new column (index + 1)
- merge the current query with itself (on the index and index+1 columns)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.