CODEWITHSUNDEEP
excelexcel-formulaformattingoffice365powerquery
Nick
Nick

Reputation: 875

Split data grouped within cells from multiple columns into rows using Power Query Editor

Similar to a beginners question I posted: Split values in cell into columns and rows

When trying to achieve the same affect for multiple columns, power query editor can split one column as desired but for the other column copies all of the values to the split into each new row (as in the image). This makes sense however im wondering if its possible to split the data accordingly as shown in the desired outcome.

I have found a work around to this by repeating the PQE exercise twice for each column to the split and then moving the outputted columns so that they are adjacent. However this seems like an inefficient way to achieve this. Can power query split both columns as desired without having to do this twice?

enter image description here

Upvotes: 0

Views: 2346

Answers (2)

horseyride
horseyride

Reputation: 21428

try below

The key is in the added custom columns that split on linefeed into lists, and then combine those lists into a table that can be expanded into rows. To make null handling easier I converted nulls to a text null, then back at end

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sub", type text}, {"CAS", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"[null]",Replacer.ReplaceValue,{"Sub", "CAS"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.Split([Sub],"#(lf)")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([CAS],"#(lf)")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns({[Custom],[Custom.1]})),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "Custom.2", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Sub", "CAS", "Custom", "Custom.1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","[null]",null,Replacer.ReplaceValue,{"Column1", "Column2"})
in #"Replaced Value1"

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60494

I would suggest first combining the columns; then doing the split.

But when you combine the columns, you need to do this on a row-by-row basis to keep things together on the same line.

  • A list of each cell contents can be created with the Text.Split function.
  • Then the two lists can be combined using the List.Zip function.
  • Finally, we just split them up.

I use a Custom Column to create the joined lists. You can see the formula by clicking on the Added Custom applied step.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Sub", type text}, {"CAS", type text}}),
    
    //combine the two columns
    #"Added Custom" = Table.AddColumn(#"Changed Type", "list", each List.Zip({
            Text.Split([Sub],"#(lf)"),
            Text.Split([CAS],"#(lf)")
            })),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sub", "CAS"}),

    //Expand the list and split into rows
    #"Expanded list" = Table.ExpandListColumn(#"Removed Columns", "list"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded list", {"list", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "list", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"list.1", "list.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"list.1", type text}, {"list.2", type text}}),

    //Rename the splitted columns
    renamed = Table.RenameColumns(#"Changed Type1",List.Zip({Table.ColumnNames(#"Changed Type1"),Table.ColumnNames(Source)}))
    
in
    renamed

enter image description here

Upvotes: 2

Related Questions