Forum Discussion
Divide 1 line
Voilá:
I added 1 line at the bottom to remove blank lines.
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}}), CountNewRows = List.Count(List.Combine(Table.Column(Table.TransformColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, Lines.FromText}}),Table.ColumnNames(#"Changed Type"){0}))), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), LinesFromText = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Lines.FromText}}), #"Expanded Value" = Table.ExpandListColumn(LinesFromText, "Value"), #"Added Index" = Table.AddIndexColumn(#"Expanded Value", "Index", 0, 1), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1), #"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"Index", each Number.Mod(_, CountNewRows), type number}}), #"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Attribute]), "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",Table.ColumnNames(#"Changed Type")), #"Removed Blank Rows" = Table.SelectRows(#"Reordered Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))) in #"Removed Blank Rows"
Sean Sure:
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}}),
CountNewRows = List.Count(List.Combine(Table.Column(Table.TransformColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, Lines.FromText}}),Table.ColumnNames(#"Changed Type"){0}))),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
LinesFromText = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Lines.FromText}}),
#"Expanded Value" = Table.ExpandListColumn(LinesFromText, "Value"),
#"Added Index" = Table.AddIndexColumn(#"Expanded Value", "Index", 0, 1),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"Index", each Number.Mod(_, CountNewRows), type number}}),
#"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Attribute]), "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1", Table.ColumnNames(#"Changed Type")),
#"Removed Blank Rows" = Table.SelectRows(#"Reordered Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"
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 -
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.
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).
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.