Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
13 | |
10 | |
8 | |
7 |
User | Count |
---|---|
17 | |
10 | |
7 | |
6 | |
6 |