Divide 1 line

[steeve695]

How do I divide 1 line into another in a table? I want to divide the selected line into 2 lines.

Thank you 🙂

[MarcelBeug]

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.

[steeve695]

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?

[steeve695]

Yes, a DAX solution should be better because I import my data from a website not Excel.

[MarcelBeug]

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.

[steeve695]

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.

[MarcelBeug · Accepted solution]

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"

[steeve695]

Thank you very much 🙂

[MarcelBeug]

You're welcome.

Here is a video to illustrate how the code was created.

1 Small correction (not in the video) because the last line from the original table is moved to the top.

This can be prevented by adding a step after step "Calculated Modulo":

select column Index, choose "Replace Values", enter 0 as from and 1 as to and then adjust the code and replace 1 by CountNewRows.

= Table.ReplaceValue(#"Calculated Modulo",0,CountNewRows,Replacer.ReplaceValue,{"Index"})

Edit: or better: the Index that was created before step "Calculated Modulo" should have started at 0 instead of 1.

Then the original sort order would have been preserved and no correction is required after that step.

[Sean]

@MarcelBeug

Can you post the final code?

[MarcelBeug]

@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"

[nickchobotar]

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 -

[MarcelBeug]

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.

[nickchobotar]

@MarcelBeug

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]

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).

[nickchobotar]

@MarcelBeug

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.

[MarcelBeug]

This was 1 solution, but coincidentally I created a very cool function yesterday as an answer to a question on the Technet forum (scroll down to the end), that can also be used here.

The problem to be solved: if you have a table with multiple nested list columns, if you expand those one by one, you get some kind of Cartesian product.

Example: this is a part of what you get after expanding the first 2 list columns after step "LinesFromText": after expanding "Mois", the column "jan."  still has 3 values in its list for e.g. at Record de froid (.C). After expanding this column, that row is triplicated etcetera.

So the solution I provided here, was to use unpivot to get all those lists in 1 column, then expand, and then pivot back.

For the other solution I created a dynamic function that expands all columns with nested lists, without resulting in a Cartesian product of rows (using Table.FromColumns). The very cool part of this function is that it automatically detects columns with nested lists.

The code of that function (ExpandListColumns):

(Table1 as table) as table => 
let
    Source = Table1,
    TableSchema = Table.Schema(Source),
    ColumnNames = Table.SelectColumns(TableSchema,{"Name"}),
    IsListColumn = Table.AddColumn(ColumnNames, "IsListColumn?", each List.AllTrue(List.Transform(Table.Column(Source,[Name]), each _ is list))),
    NonListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = false)),
    NonListColumnNames = Table.RemoveColumns(NonListColumns,{"IsListColumn?"})[Name],
    SelectNonListColumns = Table.SelectColumns(Source,NonListColumnNames),
    ListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = true)),
    ListColumnNames = Table.RemoveColumns(ListColumns,{"IsListColumn?"})[Name],
    SelectListColumns = Table.SelectColumns(Source,ListColumnNames),
    TableFromLists = Table.AddColumn(SelectListColumns, "TableFromLists", each Table.FromColumns(Record.FieldValues(_))),
    ListTables = Table.SelectColumns(TableFromLists,{"TableFromLists"}),
    Custom1 = Table.FromColumns({Table.ToRecords(SelectNonListColumns),Table.ToRecords(ListTables)}),
    #"Expanded Column1" = Table.ExpandRecordColumn(Custom1, "Column1", Table.ColumnNames(#table(List.Min({1,List.Count(NonListColumnNames)}),{})), NonListColumnNames),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"TableFromLists"}, {"TableFromLists"}),
    #"Expanded TableFromLists" = Table.ExpandTableColumn(#"Expanded Column2", "TableFromLists", Table.ColumnNames(#table(List.Count(ListColumnNames),{})), ListColumnNames),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded TableFromLists",ColumnNames[Name])
in
    #"Reordered Columns"

Applying this function to the weather statistics in Lyon:

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}}),
    LinesFromText = Table.TransformColumns(#"Changed Type",{},Lines.FromText),
    Result = ExpandListColumns(LinesFromText),
    #"Removed Blank Rows" = Table.SelectRows(Result, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

I think this solution is so cool, it might even break Lyon's record of December 22, 1938.