Forum Discussion

Ramh84's avatar
Ramh84
Frequent Visitor
1 year ago
Solved

Recurrence Problem in Power BI

Good day everyone, I am facing some techinical difficulities to move from the input table below, to the desired table. My goal is to print the recurrence of the each Merged item (Merged Header) a...
Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    You can group by 'Merged', choose 'all rows' and then add an index column starting at one for each nested table. Expand the nested tables with the index column and you will have your result.
    Example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRThikMxLpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"nestedTables", each _, type table [Column1=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"nestedTables", each Table.AddIndexColumn(_, "Recurrence", 1, 1, Int64.Type)}}),
    #"Expanded nestedTables" = Table.ExpandTableColumn(Custom1, "nestedTables", {"Recurrence"}, {"Recurrence"})
in
    #"Expanded nestedTables"
jgeddes's avatar
jgeddes
Super User
1 year ago

You can group by 'Merged', choose 'all rows' and then add an index column starting at one for each nested table. Expand the nested tables with the index column and you will have your result.
Example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRThikMxLpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"nestedTables", each _, type table [Column1=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"nestedTables", each Table.AddIndexColumn(_, "Recurrence", 1, 1, Int64.Type)}}),
    #"Expanded nestedTables" = Table.ExpandTableColumn(Custom1, "nestedTables", {"Recurrence"}, {"Recurrence"})
in
    #"Expanded nestedTables"