Forum Discussion

JIGAR's avatar
JIGAR
Icon for Resolver IV rankResolver IV
5 years ago
Solved

Repeated Index based on 2 grouped by columns

Hello,    I have data in the below format as shown in column Category and Date.    My objective is to introduce an Index column using Power Query (not DAX) as shown in Index column    I need in...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0lEy1jcy0TcyMDJUitWhgZAZdYQ8goBC5vpGRjQQigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"ByCategory", each _, type table [Category=nullable text, Date=nullable text, Index=nullable text]}}),
    #"Added Index" = Table.TransformColumns(#"Grouped Rows", {"ByCategory", each Table.AddIndexColumn(Table.Group(_, "Date", {"ByDate", each _}), "Index", 1)}),
    #"Expanded ByCategory" = Table.ExpandTableColumn(#"Added Index", "ByCategory", {"ByDate", "Index"}, {"ByDate", "Index"}),
    #"Expanded ByDate" = Table.ExpandTableColumn(#"Expanded ByCategory", "ByDate", {"Date"}, {"Date.1"})
in
    #"Expanded ByDate"
CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0lEy1jcy0TcyMDJUitWhgZAZdYQ8goBC5vpGRjQQigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Date = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"ByCategory", each _, type table [Category=nullable text, Date=nullable text, Index=nullable text]}}),
    #"Added Index" = Table.TransformColumns(#"Grouped Rows", {"ByCategory", each Table.AddIndexColumn(Table.Group(_, "Date", {"ByDate", each _}), "Index", 1)}),
    #"Expanded ByCategory" = Table.ExpandTableColumn(#"Added Index", "ByCategory", {"ByDate", "Index"}, {"ByDate", "Index"}),
    #"Expanded ByDate" = Table.ExpandTableColumn(#"Expanded ByCategory", "ByDate", {"Date"}, {"Date.1"})
in
    #"Expanded ByDate"