Forum Discussion

dongho's avatar
dongho
New Member
5 years ago
Solved

Handling rows with multiple items

Dear power users. 

 

I am struggling to handle rows with multiple items.

 

For example, the original table looks below(Some rows have multiple items on each columns)

 

              Column1           Column2             Column3

Row1    item1                   value1                  att1
             itme2                   value2                  att2

Row2    item3                   value3                  att3

             item4                   value4                  att4

 

I like to have a new table with 4 rows like below

 

              Column1           Column2             Column3

Row1    item1                   value1                  att1
Row2    itme2                   value2                  att2

Row3    item3                   value3                  att3

Row4    item4                   value4                  att4

 

How can I get this? Thanks in advance.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, dongho 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may try the following m codes in 'Advanced Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyizJTTWMycssSc01UtJRKkvMKQXxwTRIILGkBMgFkkZKsTog5am5xhDlJjDlxlDlJhDlxmDlJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{{"Column1",each Text.Split(_,"#(lf)")},{"Column2",each Text.Split(_,"#(lf)")},{"Column3",each Text.Split(_,"#(lf)")}}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each let x = List.Count([Column1]) in
List.Generate(
    ()=>0,
    each _<x,
    each _+1
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    Custom2 = Table.TransformRows(#"Expanded Custom",each 
let c = [Custom] in
[Column1=_[Column1]{c},Column2=_[Column2]{c},Column3=_[Column3]{c}]),
    #"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Column1", "Column2", "Column3"}, {"Column1.Column1", "Column1.Column2", "Column1.Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.Column1", "Column1"}, {"Column1.Column2", "Column2"}, {"Column1.Column3", "Column3"}})
in
    #"Renamed Columns"

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey there. If the blanks in the first column are nulls, you can just Table.FillDown the first column. 
    ---Nate

    • dongho's avatar
      dongho
      New Member
      5 years ago

      Rows/Columns are just headers. I want to separete the contents of the columns into multiple rows. 

      • PijushRoy's avatar
        PijushRoy
        Community Champion
        5 years ago

        Hi,

         

        If you want to above solution, make below steps or copy the code

        1. Insert Index column

        2. Insert Custom Column and insert value as ="ROW"

        3. Change data type of above both column into TEXT

        4. Marge both column data with &

        5. Remove unnecessary code

        dongho wrote:

        Rows/Columns are just headers. I want to separete the contents of the columns into multiple rows. 

        let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column4", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each "ROW"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}, {"Index", type text}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Custom]&[Index]),
        #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Column4", "Custom.1", "Column1", "Column2", "Column3", "Index", "Custom"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column4", "Index", "Custom"})
        in
        #"Removed Columns"

         

        If you find the solution, please mark as solved and click on thumbsup

         

        Thanks

        Pijush

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello dongho 

     

    I don't really know what is your final goal. But to reproduce your final table you can use a Index-column and a Table.ReplaceValue. Here an example

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUSpOSQOSiSAqVidaCcIGiSdCRYDqjGB8JGGEEERvLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, Column1 = _t, Columns = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", type text}, {"Column1", type text}, {"Columns", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    Replace = Table.ReplaceValue
    (
        #"Added Index",
        each [Row],
        each "Row"&Text.From(_[Index]),
        Replacer.ReplaceValue,
        {"Row"}
    ),
    #"Removed Columns" = Table.RemoveColumns(Replace,{"Index"})
in
    #"Removed Columns"

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, dongho 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may try the following m codes in 'Advanced Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyizJTTWMycssSc01UtJRKkvMKQXxwTRIILGkBMgFkkZKsTog5am5xhDlJjDlxlDlJhDlxmDlJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{{"Column1",each Text.Split(_,"#(lf)")},{"Column2",each Text.Split(_,"#(lf)")},{"Column3",each Text.Split(_,"#(lf)")}}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each let x = List.Count([Column1]) in
List.Generate(
    ()=>0,
    each _<x,
    each _+1
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    Custom2 = Table.TransformRows(#"Expanded Custom",each 
let c = [Custom] in
[Column1=_[Column1]{c},Column2=_[Column2]{c},Column3=_[Column3]{c}]),
    #"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Column1", "Column2", "Column3"}, {"Column1.Column1", "Column1.Column2", "Column1.Column3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.Column1", "Column1"}, {"Column1.Column2", "Column2"}, {"Column1.Column3", "Column3"}})
in
    #"Renamed Columns"

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.