Splitting and pivoting a messy column

[Jessi]

I have a column that is formatted like this:

attr1=text,attr2=more text,attr3=some more text, etc.

To separate this information, I split the column into rows by "," then into columns by "=" then pivoted which is straightforward. However, it turns out, the text itself often includes commas. Each row has a different number of these attributes. Additionally, the attributes themselves are not named in any easily isolatable way. It is very messy indeed. In reality, the column looks more like this:

a_list_of_things=first thing, second thing, and third thing,lights=the first light model number, second light model number, and third light model number,color_palette=sunrise, etc.

I would like to transform it into this:

ID	a_list_of_things	lights	color_palette
1	first thing, second thing, and third thing	the first light model number, second light model number, and third light model number	sunrise
2	null	an unspecified number of lights	sunrise
3	some more things	some very bright lights, and some dim ones	null

where null means that row did not have that particular attribute.

My initial split by "," caused the pivot to create a massive number of columns that were not useful at all. My question to you is: how do I handle this?

I have a guess, but I do not know how to execute it. If I could do a substitution of every instance of the string ",[something]=" into "|[something]=" I could split on the "|" character instead, but even then, the formula wouldn't know which "," to look for. I'm at a loss. Any help would be appreciated!

[v-easonf-msft]

Hi , @Jessi 
Can you explain it more?

The initial sample data and expected results will help us better understand your problem

Best Regards,
Community Support Team _ Eason

[Jessi]

Hello @v-easonf-msft and @camargos88 !

The data might start out looking like this:

ID	Name	Price	Attributes
1	Robot1	50.00	a_list_of_things=first thing, second thing, and third thing,lights=the first light model number, second light model number, and third light model number,color_palette=sunrise
2	Robot2	100.00	lights=an unspecified number of lights,color_palette=sunrise
3	Robot3	50.00	a_list_of_things=some more things,lights=some very bright lights, and some dim ones

And I would like to turn it into a table that looks like this:

ID	Name	Price	a_list_of_things	lights	color_palette
1	Robot1	50.00	first thing, second thing, and third thing	the first light model number, second light model number, and third light model	sunrise
2	Robot2	100.00	null	an unspecified number of lights	sunrise
3	Robot3	50.00	some more things	some very bright lights, and some dim ones	null

The attributes themselves contain a mixture of text, numbers, and, importantly, commas. There are more than 3 attributes, but this is just an example set. Does that help?

[camargos88]

Hi @Jessi ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVBBCoMwEPzKkrMUrfToJ3oVCdGsGojZko2F/r6amPYinjI7Q2Z3pm1FJQrxpJ7CDh7lrSy3V0lrOEgaZZiNm7gZjecAcSiAcSCn86QS9JmwZpoDN2FGSL8iAQtptODWpUf/sziT/n4n6kCWvHwpiyFgw6vzhlF0RSvuOccOqvIIchyjHKyOXziY0aA+zIDGtIIvbOtsW1/Vw7TgdqjH1AHnEiL/Rv+B3scwx74YMoraLEAOWXTdFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Price = _t, Attributes = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Price", Int64.Type}, {"Attributes", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "table", each Table.PromoteHeaders(Table.Transpose(Table.FromList(
Text.Split(Text.RemoveRange(Text.Combine(List.Transform(Text.Split([Attributes], ","),
each if Text.Contains(_, "=") then "**" & _ else _)), 0, 2),"**"), Splitter.SplitTextByDelimiter("="))))),
#"Expanded table" = Table.ExpandTableColumn(#"Added Custom", "table", {"a_list_of_things", "lights", "color_palette"}, {"a_list_of_things", "lights", "color_palette"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded table",{"Attributes"})
in
#"Removed Columns"

[Jessi]

Hello @camargos88 ,

This works great for the sample data! I also learned a lot about making subtables, so thank you for that. 😀

I have one more question. Is there a way to do this without knowing the column names ahead of time?

[camargos88 · Accepted solution]

Hi @Jessi ,

Nice it worked!

Try this code for a dynamic column names:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVBBCoMwEPzKkrMUrfToJ3oVCdGsGojZko2F/r6amPYinjI7Q2Z3pm1FJQrxpJ7CDh7lrSy3V0lrOEgaZZiNm7gZjecAcSiAcSCn86QS9JmwZpoDN2FGSL8iAQtptODWpUf/sziT/n4n6kCWvHwpiyFgw6vzhlF0RSvuOccOqvIIchyjHKyOXziY0aA+zIDGtIIvbOtsW1/Vw7TgdqjH1AHnEiL/Rv+B3scwx74YMoraLEAOWXTdFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Price = _t, Attributes = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Price", Int64.Type}, {"Attributes", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "table", each Table.PromoteHeaders(Table.Transpose(Table.FromList(Text.Split(Text.RemoveRange(Text.Combine(List.Transform(Text.Split([Attributes], ","), each if Text.Contains(_, "=") then "**" & _ else _)), 0, 2),"**"), Splitter.SplitTextByDelimiter("="))))),
#"Expanded table" = Table.ExpandTableColumn(#"Added Custom", "table", List.Distinct(List.Combine(List.Transform(#"Added Custom"[table], each Table.ColumnNames(_)))))
in
#"Expanded table"

The bold part gets the column names and expand them.

[Jessi]

Hi @camargos88 ,

I tried this with my real data this afternoon, and it worked like a charm! Thank you so much for your help. 🙂

[camargos88]

@Jessi ,

Can you post some data as example ?