Expand multiple lists within a column (import from web)

[ruthpozuelo]

Hi,

I am trying to import data from a website in power bi,but some of the data is showing as a list within one colum:

Can I expand all lists in "one go"? I can expand one by one and append, but I was wondering if there is a more effective way to do this.

Thanks,

Ruth

[ImkeF · Accepted solution]

Hi Ruth,

this is one possible solution:

let
    Source = Web.Page(Web.Contents("https://www.rio2016.com/en/medal-count-country")),
    Data = Source{0}[Data],
    #"Added Index" = Table.AddIndexColumn(Data, "Index", 0, 1),
    GrabDataFromNextRow = Table.AddColumn(#"Added Index", "Custom", each if Number.IsEven([Index]) then Data[#""]{[Index]+1} else "skip"),
    #"Filtered Rows1" = Table.SelectRows(GrabDataFromNextRow, each [Custom] <> "skip"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Index"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value"),
    PickContentAndTransformToTable = Table.AddColumn(#"Pivoted Column", "Custom.1", each Table.FromColumns({{[Custom]{1}}})),
    #"Expanded Custom.2" = Table.ExpandTableColumn(PickContentAndTransformToTable, "Custom.1", {"Column1"}, {"Column1"}),
    TransformNonListsToTable = Table.AddColumn(#"Expanded Custom.2", "Custom.1", each try [Column1] otherwise Table.Transpose(Table.FromColumns({List.RemoveItems(Text.Split([Custom], "#(lf)"), {Text.Split([Custom], "#(lf)"){1}})}))),
    #"Removed Columns" = Table.RemoveColumns(TransformNonListsToTable,{"Custom", "Column1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom.1","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Filled Down",{{"Column2", Text.Trim}, {"Column3", Text.Trim}, {"Column4", Text.Trim}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column2", Text.Clean}, {"Column3", Text.Clean}, {"Column4", Text.Clean}})
in
    #"Cleaned Text"

If you copy the code into the advanced editor it should run as it is. Provided that you don't use a Microsoft Internet Explorer or Edge, but Firefox or Chrome. MS editors produce crazy errors when copying M-code.

The reason for your error-message might have been missing curly brackets.

[ankitpatira]

@ruthpozuelo Never had to deal with this before but this is something I've found on technet which maybe helpful to you.

[ruthpozuelo]

Hi,

I saw that too, but it didn't work for me I get an error:

Expression.Error: We cannot convert the value " ..." to type List.

This is the table I am trying to import that has nested rows: 

https://www.rio2016.com/en/medal-count-country

I am tring to get the medals by athlele. I can use the athele table as the rows are hidden in a "load more" script.

/Ruth

[jahida]

Not sure if this is possible in one step since some are of type list and some of type text. I would do it in the following steps:

Make 1 table with the only the rows that have list

Make another table with only the rows that have strings

Expand all rows in the table with lists

Append one query to the other

If the intial ordering was important, you could add an index column before any of these steps and sort by that after.

If you need more specific guidance on the M to pull this off, let me know.

[ruthpozuelo]

Hi Jahida,

How do I expand all lists? Table.FromColumns gives me an error.

Thanks for your help,

Ruth

[ImkeF · Accepted solution]

Hi Ruth,

this is one possible solution:

let
    Source = Web.Page(Web.Contents("https://www.rio2016.com/en/medal-count-country")),
    Data = Source{0}[Data],
    #"Added Index" = Table.AddIndexColumn(Data, "Index", 0, 1),
    GrabDataFromNextRow = Table.AddColumn(#"Added Index", "Custom", each if Number.IsEven([Index]) then Data[#""]{[Index]+1} else "skip"),
    #"Filtered Rows1" = Table.SelectRows(GrabDataFromNextRow, each [Custom] <> "skip"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Index"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value"),
    PickContentAndTransformToTable = Table.AddColumn(#"Pivoted Column", "Custom.1", each Table.FromColumns({{[Custom]{1}}})),
    #"Expanded Custom.2" = Table.ExpandTableColumn(PickContentAndTransformToTable, "Custom.1", {"Column1"}, {"Column1"}),
    TransformNonListsToTable = Table.AddColumn(#"Expanded Custom.2", "Custom.1", each try [Column1] otherwise Table.Transpose(Table.FromColumns({List.RemoveItems(Text.Split([Custom], "#(lf)"), {Text.Split([Custom], "#(lf)"){1}})}))),
    #"Removed Columns" = Table.RemoveColumns(TransformNonListsToTable,{"Custom", "Column1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom.1","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Filled Down",{{"Column2", Text.Trim}, {"Column3", Text.Trim}, {"Column4", Text.Trim}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column2", Text.Clean}, {"Column3", Text.Clean}, {"Column4", Text.Clean}})
in
    #"Cleaned Text"

If you copy the code into the advanced editor it should run as it is. Provided that you don't use a Microsoft Internet Explorer or Edge, but Firefox or Chrome. MS editors produce crazy errors when copying M-code.

The reason for your error-message might have been missing curly brackets.

[ruthpozuelo]

HI Imke!

Absolutely brilliant!! Thanks!!

/Ruth

[ruthpozuelo]

One last question, I understand all the steps except this one:

try [Column1] otherwise Table.Transpose(Table.FromColumns({List.RemoveItems(Text.Split([Custom], "#(lf)"), {Text.Split([Custom], "#(lf)"){1}})}))

What is "#(lf)"? It splits the rows, it removes something (the spaces?) and then transposes from rows to columns, creates a table from the columns and then transposes again? And what does "try" do? check if it is a list?

Thanks again, Ruth 

[ImkeF · Accepted solution]

Sure:

Try ... otherwise is the ifferror-equivalent: Column1 has the desired table for the multi-medals and and error for the one-medals. So we retrieve the data from this column, but if there is an error we do sth else:

Grab the data from column "Custom" and split it (Text.Split) with "#(lf)" as delimiter, which is a linefeed. This returns a list with 5 elements, of which the second one is empty/filled with spaces.

Our aim now is to transform this list into the same format than the tables from Column1, which have 4 columns (in the same order than this text-field) but we have to remove the second element with the blanks. That way we will be able to expand them all in one step.

So we have to get rid of the 2nd element in these lists: Using "List.RemoveItems". Text.Split([Custom], "#(lf)") stands for our list and {1} actually identfies the 2nd item because M starts to count at zero. We have to put this in curly brackets because it needs to be in list-format. Now we have our 4 desired items in list-format.

Next step is to transform this into a table format: "Table.FromColumns" and to transpose, which creates exactly the 4 columns we need: "Table.Transpose".

Hope this is understandable, otherwise please ask 🙂

[ruthpozuelo]

Thanks, I got it!

/Ruth