Forum Discussion
steeve695
9 years agoNew Member
Divide 1 line
How do I divide 1 line into another in a table? I want to divide the selected line into 2 lines. Thank you :)
- 9 years ago
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"
nickchobotar
9 years agoSkilled Sharer
It starts to make sense. May I pelase also know why CountNewRows statements returns 11 when we have 9 separate lines inside 7 rows ?
MarcelBeug
9 years agoCommunity Champion
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).
- nickchobotar9 years agoSkilled Sharer
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.