Forum Discussion
Custom Index / Partition Dense Rank in Power Query M
I'm trying to achieve a dense ranking in Power Query so I can produce the following output where I have two "levels" of ranking as seen in the below screenshot. I'm pulling from SAP HANA but the Window functions are not working properly so I have to use Power Query instead of using SQL script in SAP HANA. I can't use DAX either because I want to build a custom Key in Power Query that concatenates the two "levels" of ranking(as well as other fields) where the Key will then be used as part of creating a relationship in my Model to another table.
Is this possible in Power Query M to create this in a fairly straightforward manner?
The only thing I can think of is creating two more queries(based off my main query) to pull from HANA but at a higher-level of granularity; one at the Prod Line level and the other at the Shift level. Then for each query I would leverage Power Query M to build a custom Index to perform the ranking and then join(merge) the results from both queries to my main query/table that I'm already pulling in.
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
4 Replies
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