Please help -should I Pivot or Split by Delimiter?

[PhoenixBird66]

Hi Guys,

I've got some data which is really messy. In some columns we have multiple values separated with a comma, and then in another column they have percentages also separated with a comma, like in the image below.

I need to be able to match up the percentage with the correct process - so for ID 1234 I need 3 lines of data:

Annealing is 20%

Brazing is 20% 

Canning is 60%

I dont know how best to do this -should I split the columns by delimiter, or should I be unpivoting them?

[v-yuaj-msft · Accepted solution]

Hi,

Based on your description, you can do some steps as follows.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[mahoneypat]

Did you try the approach in my suggestion?  It should even with mutliple columns with comma delimited lists in them.  You would just add a third Text.Split inside the List.Zip part, and a third column name inside the Record.FromList part.

Regards,

Pat

[PhoenixBird66]

I did try it, thanks. However I'm not good with M code (I'm not that advanced) and I dont know how to apply what you've given me to the data in my table? 😞

It'd be easier for me to do manual steps - but I dont understand which step to do first. 

[mahoneypat]

Here is another way to do in the query editor in mostly one step, making a custom column that makes a List of Records from the columns.  I did an example with letters and numbers, but note that both input column need to be text format (you can convert to number after, if needed).

To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hci3DcAwEATBXi7ehKRsKEOZGh7ffxs6KBrsRqgIbewctlBpSkLVddK57MDI9N/munl47czCqswP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Letters = _t, Numbers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Letters", type text}, {"Numbers", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform(List.Zip({Text.Split([Letters],","), Text.Split([Numbers],",")}), each Record.FromList(_, {"Letter", "Number"}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Letters", "Numbers"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Letter", "Number"}, {"Letter", "Number"})
in
    #"Expanded Custom1"

Regards,

Pat

[HotChilli]

The answer is -> both.

The pattern for these things is :

Split the columns

Select the key column (the first here) and Unpivot other columns.

You will get an Attribute column which holds information that we can use (1,2,3 or column2 column3)

Split the Attribute column (on ".")

You then Pivot the Attribute.1 column. Use Value in Values and choose Advanced-> Don't Aggregate.

Let me know how you get on

[PhoenixBird66]

Thanks for your input on this. 

Unfortunately it's a little more complicated than I mentioned earlier because there's an additional column with more then one value in (so that also needs to be split somehow)

This is where I've got it to now. Not sure which columns should be unpivoted and split and pivoted again and in what order?