Split two columns by delimiter out of many to create new rows

[mbowler]

I've looked at multiple posts about splitting several columns by delimiter but haven't quite found what I'm looking for. My data is something like this:

Product data1	Product data2	States	Rank	etc
Value1.1	Value1.2	AZ	2
Value2.1	Value2.2	AZ; FL	1; 4
Value3.1	Value3.2	FL; NJ; NY	2; 5; 6

What I want to achieve is the following by splitting States and Rank but keeping the ordering between the ";" :

Product data1	Product data2	States	Rank	etc
Value1.1	Value1.2	AZ	2
Value2.1	Value2.2	AZ	1
Value2.1	Value2.2	FL	4
Value3.1	Value3.2	FL	2
Value3.1	Value3.2	NJ	5
Value3.1	Value3.2	NY	6

Bear in mind that there are many more columns that I just want to keep and data be duplicated for each new row. I tried the following but it failed at the last step:

1. Unpivot colums States & Rank  (Creates columns Attribute & Value)
2. Split colum (Value) by ";" delimiter into rows
3. Pivot Attribute coulmn using Value as values (don't aggregate)

[SundarRaj]

Hi @mbowler , here's a solution you could take a look at. I'll just attach the images of the output and M code with respect to the source table you mentioned. Have a look. Thanks!

[wdx223_Daniel]

NewStep=#table(Table.ColumnNames(YourSourceName),List.TransformMany(Table.ToRows(YourSourceName),each List.Zip(List.Transform(List.Skip(_,2),each Text.Split(_,";"))),(x,y)=>List.FirstN(x,2)&y))

[mbowler]

Thanks @wdx223_Daniel , I love the succinctness of this, though I'll have to study it further to understand what it is doing.

One concern is though, as there doesn't seem to be any references to the State and Rank columns, would this try to split any column with a semi-colon? My data has other columns that contain semi-colons and I don't want them split but just copied as per normal.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[mbowler]

@lbendlin I'm marking your response as Solution, my data actually has many more columns before and after the ones I need to split over (plus they are not consecutive), so @wdx223_Daniel version that skips columns won't work. Your version where the columns are pulled out by name works great.

[lbendlin · Accepted solution]

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU011DNU0oExjYBMxyggYaQUqwNVYIRQYARVYK3g5gNkGFormCDUGSPUGYPVuflYK/h5AXEkyEBrBVNrBTOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product data1" = _t, #"Product data2" = _t, States = _t, Rank = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,null,null,(x,y,z)=>Text.Split(x,"; "),{"States","Rank"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.Zip({[States],[Rank]})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"States", "Rank"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"State", "Rank"})
in
    #"Split Column by Delimiter"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source. You can make the list of columns dynamic too if you want.

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU011DNU0oExjYBMxyggYaQUqwNVYIRQYARVYK3g5gNkGFormCDUGSPUGYPVuflYK/h5AXEkyEBrBVNrBTOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product data1" = _t, #"Product data2" = _t, States = _t, Rank = _t]),
    Cols = List.Skip(Table.ColumnNames(Source),2),
    #"Replaced Value" = Table.ReplaceValue(Source,null,null,(x,y,z)=>Text.Split(x,"; "),Cols),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.Zip(Record.FieldValues(Record.SelectFields(_,Cols)))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",Cols),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), Cols)
in
    #"Split Column by Delimiter"

[mbowler]

Thanks this worked great. I did have to make a few tweaks because one of the columns (eg Rank) is entered by users so I can't rely on there being a space after (or before) the semicolon, but it was pretty straight forward to fix.

What I haven't checked yet is whether the above is fault tolerant, ie what will happen if the user entered (Rank) column is empty or contains more or less semicolon separated values that the State column. I would like it to fill out with null.

Eg

Product data1	Product data2	States	Rank	etc
Value1.1	Value1.2	AZ
Value2.1	Value2.2	AZ; FL	1; 4
Value3.1	Value3.2	FL; NJ; NY	2; 5;6
Value4.1	Value4.2	NJ; CA	5; 3; 10

Becomes:

Product data1	Product data2	States	Rank	etc
Value1.1	Value1.2	AZ	null
Value2.1	Value2.2	AZ	1
Value2.1	Value2.2	FL	4
Value3.1	Value3.2	FL	2
Value3.1	Value3.2	NJ	5
Value3.1	Value3.2	NY	6
Value4.1	Value4.2	NJ	5
Value4.1	Value4.2	CA	3
Value4.1	Value4.2	null	10

[mbowler]

Looks like the missing values in Rank do ceate rows with null but too many values creates a row with the rank value in the States column:

Product data1	Product data2	States	Rank	etc
Value1.1	Value1.2	AZ	null
...	....	...	...	...
Value4.1	Value4.2	10	null

I can cope with this though, so all good, thanks everyone!