Handling rows with multiple items

[dongho]

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.

[v-alq-msft · Accepted solution]

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.

[Jimmy801]

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

[watkinnc]

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

[dongho]

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

[PijushRoy]

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