Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

splitting a cell with multiple entries

StateFruit

Quantity

California

Apple

Pear

Grapes

12

15

30

I have this huge problem I inherited 😞

There are multiple entries in a cell and I would like to give each line within the fruit cell its own row so I can sum up the quantities.

I'm sure this is already solved. Can someone share the link to this solution? 😞

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

    Hi, Anonymous 

     

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

    Table:

     

    Here are the m codes for transformations in 'Advanced Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MyUzLL8rLTFTSUXIsKMhJjckLSE0sislzL0osSC0GihoaxeQZmsbkGRsoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Fruit = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Fruit", type text}, {"Quantity", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{{"Fruit",each Text.Split(_,"#(lf)")},{"Quantity",each Text.Split(_,"#(lf)")}}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each let f=[Fruit] in
List.Generate(
    ()=>0,
    each _<=List.Count(f)-1,
    each _+1
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "NewFruit", each [Fruit]{[Custom]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NewQuantity", each [Quantity]{[Custom]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Fruit", "Quantity", "Custom"})
in
    #"Removed Columns"

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous 

    Can you share some source data in a file please?  It's not clear whether the data as shown are in merged cells or what the delimiter is between words and values.  Spaces?

    Phil

    If I answered your question please mark my post as the solution.

    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

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

    Hi, Anonymous 

     

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

    Table:

     

    Here are the m codes for transformations in 'Advanced Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MyUzLL8rLTFTSUXIsKMhJjckLSE0sislzL0osSC0GihoaxeQZmsbkGRsoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Fruit = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Fruit", type text}, {"Quantity", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{{"Fruit",each Text.Split(_,"#(lf)")},{"Quantity",each Text.Split(_,"#(lf)")}}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each let f=[Fruit] in
List.Generate(
    ()=>0,
    each _<=List.Count(f)-1,
    each _+1
)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "NewFruit", each [Fruit]{[Custom]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "NewQuantity", each [Quantity]{[Custom]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Fruit", "Quantity", "Custom"})
in
    #"Removed Columns"

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.