Divide 1 line

In the part:

Table.TransformColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, Lines.FromText}})

All values in the first table column - being Table.ColumnNames(#"Changed Type"){0} - are converted to lists, using Lines.FromText. So the first column of the table will be a column with nested lists: if a field has no line breaks, it has 1 item in the nested list; if a field has 2 line breaks, the nested list has 3 items.

From this table, the first column is taken, resulting in a list with nested lists.

These are combined to 1 list and the number of items in this list will be the number of rows after pivoting later on in the code.

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:

Hi MarcelBeug,

This is some great stuff. Could you please be so kind to explain what are you saying exactly in your CountNewRows logic and how is it applicable here?

CountNewRows = List.Count(List.Combine(Table.Column(Table.TransformColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, Lines.FromText}}),Table.ColumnNames(#"Changed Type"){0}))),

Thanks, N -

MarcelBeug

It starts to make sense.  May I pelase also know why CountNewRows statements returns 11 when we have 9 separate lines inside 7 rows ?

Rows 4 and 5 have 3 separate lines each, of which the middle row is blank (these will be removed at the very end of the query, after pivoting).

MarcelBeug

Yepp!!! It just dawned on me . I did quick test and combined with blanks we get 11 lines.

These Lines Fuctions   appear to be pretty powerful.

This is a great pattern. Thanks for your prompt reply.