Expand multiple lists within a column (import from web)

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.

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