Power Query seems to hang when I click Close and Apply

[sfog]

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

[sfog · Accepted 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)

[V-pazhen-msft]

@sfog 

Thank you for share your workaround.

Paul Zheng _ Community Support Team