Forum Discussion
Solved
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"
MarcelBeug
Community Champion
Community ChampionThat 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?
- MarcelBeug
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?
Yes, a DAX solution should be better because I import my data from a website not Excel.
