Forum Discussion
Custom Index / Partition Dense Rank in Power Query M
HI Anonymous ,
My query contains custom steps with invoke custom functions to loop table records on each row for calculating, it seems like not works on your scenario due to huge amount records. (notice: actual calculate amount may larget than 2.1M*2.1M*2)
In my opinion, I'd like to suggest you add a row index to query table then use multiple column ranking(ISONORAFTER function with group columns and custom row index column) to add index or different column groups on the data model table side.
Reference links:
Notice: row index column that I ask you to add in the query table side is used to prevent the duplicate ranking of similar group records.
Regards,
Xiaoxin Sheng
Hi Anonymous ,
I'd like to suggest you to take a look at Group and AddIndexcolumn functions, they may suitable for your requirements.
In addition, I also attach the sample below(snapshot and full query table), you can check it if helps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc8xDoAgDAXQuzCb2FaLMqoHcHAk3sD7zxYE4lBRh/KXl/C/98a12BKgM41BBJRY1q2XILnpkIfB7E0FdgEGjVCR/F/GFvwMrUSfYWlJBTImOKpzFOj0koqMWVq+01pPIHvJ+KvcPNyXK5DzIHqBNrc8PsDYsUtwPwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Plant = _t, #"Prod Line" = _t, #"Shift Number" = _t, Part = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Plant", Int64.Type}, {"Prod Line", type text}, {"Shift Number", Int64.Type}, {"Part", type text}, {"Quantity", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Plant"}, {{"C1", each _, type table [Date=date, Plant=number, Prod Line=text, Shift Number=number, Part=text, Quantity=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(Table.Group([C1],{"Plant","Prod Line"},{{"C2",each _,type table}}),"PPI",1,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each Table.AddIndexColumn(Table.Group([C1],{"Plant","Shift Number"},{{"C2",each _,type table}}),"PSI",1,1)),
#"Expanded C1" = Table.ExpandTableColumn(#"Added Custom1", "C1", {"Date", "Prod Line", "Shift Number", "Part", "Quantity"}, {"Date", "Prod Line", "Shift Number", "Part", "Quantity"}),
Custom1 = Table.ReplaceValue(#"Expanded C1",each [Custom], each Function.Invoke((para1,para2,tb as table)=> Table.SelectRows(tb,each [Plant]=para1 and [Prod Line]= para2)[PPI]{0},{[Plant],[Prod Line],[Custom]}), Replacer.ReplaceValue,{"Custom"}),
Custom2 = Table.ReplaceValue(Custom1,each [Custom1], each Function.Invoke((para1,para2,tb as table)=> Table.SelectRows(tb,each [Plant]=para1 and [Shift Number]= para2)[PSI]{0},{[Plant],[Shift Number],[Custom1]}), Replacer.ReplaceValue,{"Custom1"}),
#"Renamed Columns" = Table.RenameColumns(Custom2,{{"Custom", "PPI"}, {"Custom1", "PSI"}})
in
#"Renamed Columns"Regards,
Xiaoxin Sheng
Hi Anonymous ,
I implemented the M code you provided and it appears that it worked while in Power Query editor but when I click "Close and Apply", the "Apply query changes" window appears where you can see the amount of rows being pulled in. However, once all the rows are pulled in the "Apply query changes" window appears to "hang" or get "stuck" and nothing else happens. I'm pulling in approx. 2.1 million rows from my SAP HANA source table. I didn't have this issue before I implemented the M code.
Any idea why the "Apply query changes" window would now get "stuck" after applying the M code?
HI Anonymous ,
My query contains custom steps with invoke custom functions to loop table records on each row for calculating, it seems like not works on your scenario due to huge amount records. (notice: actual calculate amount may larget than 2.1M*2.1M*2)
In my opinion, I'd like to suggest you add a row index to query table then use multiple column ranking(ISONORAFTER function with group columns and custom row index column) to add index or different column groups on the data model table side.
Reference links:
Notice: row index column that I ask you to add in the query table side is used to prevent the duplicate ranking of similar group records.
Regards,
Xiaoxin Sheng