Forum Discussion

PowerBI_Query's avatar
PowerBI_Query
Helper II
4 years ago
Solved

Nest Column Values Under Main Column

My original data is shown as below. I want to nest respective B and C columns under A column. If A and B values are same I skip those values. Output after nesting under column A.  
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • jennratten's avatar
    jennratten
    4 years ago

    Sorry about that.  Here you are.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDoQwCAbgd2HW5NQCddWol5tJTGM63wvc4ttL42A5FsIPXwLHAQGJOZQKDayCGFCbrh9anbWEhJCbPzYXRpaRZ9OsjC3jiknaFt2aUNHo6frTFA2LNQuT7IV9ke4XHjeO3plQXTY0ieg26d3TKH559faq8+qTnOq9WnanBsj5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t, Sub = _t, #"Sub-Code" = _t]),
    #"Replaced Value" = Table.SelectRows(Source,each [Main]<>[Sub]),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Main", "Main/Sub"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Main/Sub"}, {{"Level", each 1, Int64.Type}, {"Data", each Table.RenameColumns ( Table.RemoveColumns ( Table.AddColumn ( _, "Level", each 2, Int64.Type), {"Main/Sub"} ), {{"Sub", "Main/Sub"}} ), type table }}),
    Custom1 = Table.AddColumn ( #"Grouped Rows", "NewTable", each   Table.Combine ( { Table.FromRows({Record.FieldValues(Record.SelectFields (_, {"Level", "Main/Sub"}))}, {"Level", "Main/Sub"}), [Data] } ) ),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"NewTable"}),
    #"Expanded NewTable" = Table.ExpandTableColumn(#"Removed Other Columns", "NewTable", {"Level", "Main/Sub", "Sub-Code"}, {"Level", "Main/Sub", "Sub-Code"})
in
    #"Expanded NewTable"
PowerBI_Query's avatar
PowerBI_Query
Helper II
4 years ago

There is an error in the final output. 4th row values are same in both columns I need to skip that row.

 However, I see it in the final table. Could you add an if statement to exclude it?

 

jennratten's avatar
jennratten
Super User
4 years ago

This should do it.

 

 

  • PowerBI_Query's avatar
    PowerBI_Query
    Helper II
    4 years ago

    Could you paste the modified code. I am unable to see it from the screenshot. 

    • jennratten's avatar
      jennratten
      Super User
      4 years ago

      Sorry about that.  Here you are.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDoQwCAbgd2HW5NQCddWol5tJTGM63wvc4ttL42A5FsIPXwLHAQGJOZQKDayCGFCbrh9anbWEhJCbPzYXRpaRZ9OsjC3jiknaFt2aUNHo6frTFA2LNQuT7IV9ke4XHjeO3plQXTY0ieg26d3TKH559faq8+qTnOq9WnanBsj5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Main = _t, Sub = _t, #"Sub-Code" = _t]),
    #"Replaced Value" = Table.SelectRows(Source,each [Main]<>[Sub]),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Main", "Main/Sub"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Main/Sub"}, {{"Level", each 1, Int64.Type}, {"Data", each Table.RenameColumns ( Table.RemoveColumns ( Table.AddColumn ( _, "Level", each 2, Int64.Type), {"Main/Sub"} ), {{"Sub", "Main/Sub"}} ), type table }}),
    Custom1 = Table.AddColumn ( #"Grouped Rows", "NewTable", each   Table.Combine ( { Table.FromRows({Record.FieldValues(Record.SelectFields (_, {"Level", "Main/Sub"}))}, {"Level", "Main/Sub"}), [Data] } ) ),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"NewTable"}),
    #"Expanded NewTable" = Table.ExpandTableColumn(#"Removed Other Columns", "NewTable", {"Level", "Main/Sub", "Sub-Code"}, {"Level", "Main/Sub", "Sub-Code"})
in
    #"Expanded NewTable"