splitting a cell with multiple entries

[Anonymous]

State	Fruit	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? 😞

[v-alq-msft · Accepted solution]

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.

[themistoklis]

Hello @Anonymous ,

Maybe this link will help you:

https://community.powerbi.com/t5/Desktop/How-to-split-the-the-Column-into-Multiple-rows/m-p/255014#M113552

[PhilipTreacy]

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.