Divide 1 line

This was 1 solution, but coincidentally I created a very cool function yesterday as an answer to a question on the Technet forum (scroll down to the end), that can also be used here.

The problem to be solved: if you have a table with multiple nested list columns, if you expand those one by one, you get some kind of Cartesian product.

Example: this is a part of what you get after expanding the first 2 list columns after step "LinesFromText": after expanding "Mois", the column "jan."  still has 3 values in its list for e.g. at Record de froid (.C). After expanding this column, that row is triplicated etcetera.

So the solution I provided here, was to use unpivot to get all those lists in 1 column, then expand, and then pivot back.

For the other solution I created a dynamic function that expands all columns with nested lists, without resulting in a Cartesian product of rows (using Table.FromColumns). The very cool part of this function is that it automatically detects columns with nested lists.

The code of that function (ExpandListColumns):

(Table1 as table) as table => 
let
    Source = Table1,
    TableSchema = Table.Schema(Source),
    ColumnNames = Table.SelectColumns(TableSchema,{"Name"}),
    IsListColumn = Table.AddColumn(ColumnNames, "IsListColumn?", each List.AllTrue(List.Transform(Table.Column(Source,[Name]), each _ is list))),
    NonListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = false)),
    NonListColumnNames = Table.RemoveColumns(NonListColumns,{"IsListColumn?"})[Name],
    SelectNonListColumns = Table.SelectColumns(Source,NonListColumnNames),
    ListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = true)),
    ListColumnNames = Table.RemoveColumns(ListColumns,{"IsListColumn?"})[Name],
    SelectListColumns = Table.SelectColumns(Source,ListColumnNames),
    TableFromLists = Table.AddColumn(SelectListColumns, "TableFromLists", each Table.FromColumns(Record.FieldValues(_))),
    ListTables = Table.SelectColumns(TableFromLists,{"TableFromLists"}),
    Custom1 = Table.FromColumns({Table.ToRecords(SelectNonListColumns),Table.ToRecords(ListTables)}),
    #"Expanded Column1" = Table.ExpandRecordColumn(Custom1, "Column1", Table.ColumnNames(#table(List.Min({1,List.Count(NonListColumnNames)}),{})), NonListColumnNames),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"TableFromLists"}, {"TableFromLists"}),
    #"Expanded TableFromLists" = Table.ExpandTableColumn(#"Expanded Column2", "TableFromLists", Table.ColumnNames(#table(List.Count(ListColumnNames),{})), ListColumnNames),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded TableFromLists",ColumnNames[Name])
in
    #"Reordered Columns"

Applying this function to the weather statistics in Lyon:

let
    Source = Web.Page(Web.Contents("https://fr.wikipedia.org/wiki/Lyon")),
    Data2 = Source{2}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data2,{{"Mois", type text}, {"jan.", type text}, {"fév.", type text}, {"mars", type text}, {"avril", type text}, {"mai", type text}, {"juin", type text}, {"jui.", type text}, {"août", type text}, {"sep.", type text}, {"oct.", type text}, {"nov.", type text}, {"déc.", type text}, {"année", type text}}),
    LinesFromText = Table.TransformColumns(#"Changed Type",{},Lines.FromText),
    Result = ExpandListColumns(LinesFromText),
    #"Removed Blank Rows" = Table.SelectRows(Result, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

I think this solution is so cool, it might even break Lyon's record of December 22, 1938. :smileyvery-happy: