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"
Yes, a DAX solution should be better because I import my data from a website not Excel.
Primary function of Power Query is to get and transform data from many sources, including the web.
So in this case Power Query would be the prefered solution; not DAX.
You can use my code also for data from the web, you only need to adjust the first steps that get the data.
If you can share the URL of the website, then I can adjust my code accordingly.
Ok thank you for the help.
The URL is : https://fr.wikipedia.org/wiki/Lyon and the table to load is the last in the list.
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"Thank you very much :)