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 a...
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.
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
Super User
Super UserHi 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.