Forum Discussion
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 :)
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"
17 Replies
That will not be very easy.
I created a simplified table (Table3) in Excel
and splitted all rows with multiple lines with the following code.
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mois", type text}, {"Jan", 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")) in #"Reordered Columns"Just take a look if this is the result you are looking for, and if you can follow along with code.
Otherwise I can provide a revision and/or further explanation later today.
Thanks for replying. I would to do it without using script but in modifying the request directly in PowerBI is it possible?
Yes, you can do this in Powe BI via Edit Queries (Power Query or M).
In this video you can see how I move the code from Power Query in Excel to Power Query in Power BI.
But I guess you prefer a DAX solution?
