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"
daxer-almighty's avatar
daxer-almighty
Icon for Solution Sage rankSolution Sage
5 years ago

Here's how to do it in PQ without using any intermediate table and any manual editing of the M code. Everything done by just point-and-click:

// T
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gxR0lEy1jcy0TcyMDIEsg2VYnWoKWyBJGxMUBiHIWZIwkbUE/YIAjLN9Y2MUK2kubAFkrARQWFLJGFjgsKGBkjiJqSLxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Date = _t, OriginalIndex = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Date", type text}, {"OriginalIndex", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Category", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category", "Date"}, {{"RowsToExpandTo", each _, type table [Category=nullable text, Date=nullable date, OriginalIndex=nullable number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows1" = Table.Group(#"Added Index", {"Category"}, {{"MinIndex", each List.Min([Index]), type number}, {"RowsToExpand2", each _, type table [Category=nullable text, Date=nullable date, RowsToExpandTo=table, Index=number]}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows1",{"Category", "RowsToExpand2", "MinIndex"}),
    #"Expanded RowsToExpand2" = Table.ExpandTableColumn(#"Reordered Columns", "RowsToExpand2", {"Category", "Date", "RowsToExpandTo", "Index"}, {"Category.1", "Date", "RowsToExpandTo", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded RowsToExpand2",{"Category.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "FinalIndex", each [Index] - [MinIndex] + 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index", "MinIndex", "Category", "Date"}),
    #"Expanded RowsToExpandTo" = Table.ExpandTableColumn(#"Removed Columns1", "RowsToExpandTo", {"Category", "Date", "OriginalIndex"}, {"Category", "Date", "OriginalIndex"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded RowsToExpandTo", "Is Final Equal to Original?", each [FinalIndex] = [OriginalIndex])
in
    #"Added Custom1"

Just inspect the code one step at a time and you'll be able to apply this technique to anything. Enjoy!