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    ...
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.

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