Transform Data, List into individual rows

[Anonymous]

Hi All,

I have two input values from the sharepoint list; countries and their corresponding values.Is it possible to transform both from a list into individual rows.

Left table shows how it appears on the list and right table is how I need in Power BI

Thanks

Manny

[Anonymous]

Hi Miguel,

I found one more issue withthe solution. is this something we can change? The output I got is this. The Country data is not getting repeated when I create a table. But when I do a sum, it is working. I'm missing some of the items when I create the data as a Table

[MFelix]

Hi @Anonymous,

In the query editor select the column and split rows by delimiter, check the documentation link below.

https://docs.microsoft.com/en-us/power-query/split-columns-delimiter

[Anonymous]

Hi Miguel,

This action wont let me to get the desired result, because I have multiple options on both the columns. Is there a way to split both the colums simultaneously, without affecting the values?

[MFelix · Accepted solution]

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