index per group

Hi Anonymous ,

Please check:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMVNJRMjIwMtA31DcEMhUMlWJ1sIkboYsb6RsagCSMDXDJmIElkpKSkCTMEXZgkYCahSpjAZIB2R8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, date = _t, #"item no." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"date", type date}, {"item no.", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "date"}, {{"Count", each _, type table [ID=nullable text, date=nullable date, #"item no."=nullable number]}}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"ID"}, {{"Count", each _, type table [ID=nullable text, date=nullable date, Count=table]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count],"Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"date", "Count", "Index"}, {"date", "Count.1", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Expanded Count.1" = Table.ExpandTableColumn(#"Removed Columns1", "Count.1", {"item no."}, {"item no."}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count.1", each ([#"item no."] = 30))
in
    #"Filtered Rows"

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.