Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
sfog
Advocate II
Advocate II

Power Query seems to hang when I click Close and Apply

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).

 

sfog_0-1610402047854.png

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

1 ACCEPTED SOLUTION
sfog
Advocate II
Advocate II

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)

View solution in original post

2 REPLIES 2
sfog
Advocate II
Advocate II

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)

Anonymous
Not applicable

@sfog 

Thank you for share your workaround.

 

Paul Zheng _ Community Support Team

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.