Transform Data, List into individual rows

Hi Anonymous ,

This can be done adding a new column with the following code:

Table.FromColumns ( {Text.Split ([Country], ", ") , Text.Split ([Value], ", ")})

The you just need to expand the table and delete the other columns that you don't need.

Complete code for the Query below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwtLilKzMlM1FFwc/KL0lHwzEvJz0stzkxU0lEy1VEwNNBRMDJQitWJVoLIwzUA5UGSpmA538ScxEqoJqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Value = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Country.1", each Table.FromColumns ( {Text.Split ([Country], ", ") , Text.Split ([Value], ", ")})),
    #"Expanded Country.1" = Table.ExpandTableColumn(#"Added Custom", "Country.1", {"Column1", "Column2"}, {"Country.1.Column1", "Country.1.Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Country.1",{"Country", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Country.1.Column1", "Country"}, {"Country.1.Column2", "Value"}})
in
    #"Renamed Columns"

This was based on the video below.

https://www.youtube.com/watch?v=V5X-wo0wVw0