Power Query: Splitting Multi-Valued Cells into Rows without Creating all Possible Combinations

Question

I am using Power Query in Excel for Microsoft 365.

Some of my columns have multi-valued cells. I need to split such cells into rows. Consider this source data:

The M Language code for my Power Query is as follows;

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pet Type", type text}, {"Items Needed", type text}, {"Item Purpose", type text}, {"Typical Name", type text}}),
    #"Split Items Needed" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Items Needed", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items Needed"),
    #"Split Item Purpose" = Table.ExpandListColumn(Table.TransformColumns(#"Split Items Needed", {{"Item Purpose", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item Purpose")
in
    #"Split Item Purpose"

This results in this actual result:

Here is the desired result:

How must I adjust my Power Query to get the desired result?

Answer 1

The following M Language code will solve this problem:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type of All to Text" = Table.TransformColumnTypes(Source,{{"Pet Type", type text}, {"Items Needed", type text}, {"Item Purpose", type text}, {"Typical Name", type text}}),
    #"Split Items Needed" = Table.AddColumn(#"Changed Type of All to Text", "Split Items Needed", each Text.Split([Items Needed],"#(lf)")),
    #"Split Item Purpose" = Table.AddColumn(#"Split Items Needed", "Split Item Purpose", each Text.Split([Item Purpose],"#(lf)")),
    #"Match Splits Step 1" = Table.AddColumn(#"Split Item Purpose", "Split Items", each Table.FromColumns({[Split Items Needed],[Split Item Purpose]})),
    #"Match Splits Step 2" = Table.ExpandTableColumn(#"Match Splits Step 1", "Split Items", {"Column1", "Column2"}, {"Items Needed.1", "Item Purpose.1"}),
    #"Changed New Columns to Text" = Table.TransformColumnTypes(#"Match Splits Step 2",{{"Items Needed.1", type text}, {"Item Purpose.1", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed New Columns to Text",{"Pet Type", "Typical Name", "Items Needed.1", "Item Purpose.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Items Needed.1", "Items Needed"}, {"Item Purpose.1", "Item Purpose"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Pet Type", "Items Needed", "Item Purpose", "Typical Name"})
in #"Reordered Columns"

Source:
https://stackoverflow.com/questions/62798233/splitting-multiple-multi-valued-cells-in-excel-into-rows

Credit and thanks to @horseyride for the answer at the link above.

Edit to Add Second Solution

The following solution, also with credit to the question linked above, is quite elegant in that it works across any number of columns:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    #"TableTransform" = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"), (x) => List.Transform(Record.ToList(x), each Text.Split(_, "#(lf)"))), each Table.FromColumns(_, Table.ColumnNames(#"Changed Type")))),
    #"Changed Type1" = Table.TransformColumnTypes(#"TableTransform", List.Transform(Table.ColumnNames(#"TableTransform"), each {_, type text})),
    #"Filled Down" = Table.FillDown(#"Changed Type1", Table.ColumnNames(#"Changed Type1"))
in #"Filled Down"