Nested Index (Power Query)

[Boja]

Hello,

I am trying to get a nested index column in Power Query based on the following dataset.

Index assignes the lowest rank (1) to the latest AppVersion on each Platform. And it still keeps other fields like CustomerID and Date. 

I used the code below (and some variations of it), but it doesnt give the result i want. 

#"Sorted Rows" = 
Table.Sort(#"Platform&Version",{{"AppVersion", Order.Descending}}),

#"Grouped Rows" = 
Table.Group(#"Sorted Rows", {"AppPlatform"}, {{"Count", each Table.AddIndexColumn(_ , "Index", 1,1), type table}}),

#"Expanded Count" = 
Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CustomerID", "Date",  "Platform&Version", "Index"}, {"CustomerID", "Date",  "Platform&Version", "Index"}),

I guess that index should be based on distinct values of Platform&AppVersion field, but i dont know how to implement logic into the code. Any ideas, please?

Thank you

[Zubair_Muhammad · Accepted solution]

@Boja

Please try this.

Please see the attached file for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLKcrPTFHSUbLQMzYFUol5lSUZmXnpQKaBoa5XYp6uoTmQDVWnAFYVq0NAoxG5Go2J1WiCptEEq0YTwhpNidVojKbRDKtGY8IazcnVaEGuRks8GsMz81Lyy4uxBo6hAUIjVB0icPBqNCRWI5pTDY2wasTiVHSNxrg0xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AppPlatform = _t, AppVersion = _t, CustomerID = _t, Date = _t, #"Platform&Version" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AppPlatform", type text}, {"AppVersion", type number}, {"CustomerID", type text}, {"Date", type date}, {"Platform&Version", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"AppPlatform", Order.Ascending}, {"AppVersion", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"AppPlatform", "AppVersion"}, {{"All Rows", each _, type table}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"AppPlatform"}, {{"All Rows", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "All Rows", {"AppVersion", "All Rows", "Index"}, {"AppVersion", "All Rows.1", "Index"}),
    #"Expanded All Rows.1" = Table.ExpandTableColumn(#"Expanded All Rows", "All Rows.1", {"CustomerID", "Date", "Platform&Version"}, {"CustomerID", "Date", "Platform&Version"})
in
    #"Expanded All Rows.1"

[Boja]

Hi Zubair,

This is a great solution, simple and easy.

Thank you for sharing your wisdom

have a great day. you've just made mine better 🙂

[Zubair_Muhammad]

@Boja

Also you can do the same with DAX calculated column

Calc Column =
RANKX (
    FILTER ( Table1, [AppPlatform] = EARLIER ( [AppPlatform] ) ),
    [AppVersion],
    ,
    DESC,
    DENSE
)