Forum Discussion
Transform Data, List into individual rows
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
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.
4 Replies
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
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.