Forum Discussion
Solved
Transform data from row to column
I want to convert a large dataset withfeatures similar to table 1 into a format that looks like table 2. Can you please help me? Table 1 Netcast Item Placeholder Fees 123456, 654321, 987...
Convert your Placeholder column to a List, then expand it to new rows.
Paste the code below into a blank query to understand how it works. Then you can adapt it to your actual query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNLVbSUTI0MjYxNdNRMDM1MTYy1FGwtDAHMpVidYAqIoDypmAA5vuXZKQWgfWYWhqbmusomJsaW5oaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Netcast Item" = _t, Placeholder = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Netcast Item", type text}, {"Placeholder", type text}}), #"Convert to Lists" = Table.TransformColumns(#"Changed Type", {"Placeholder", each List.Transform(Text.Split(_,","), (l)=>Text.Trim(l)), type {text}}), #"Expanded Placeholder" = Table.ExpandListColumn(#"Convert to Lists", "Placeholder"), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Placeholder",{"Placeholder", "Netcast Item"}) in #"Reordered Columns"Source
Results
Convert your Placeholder column to a List, then expand it to new rows.
Paste the code below into a blank query to understand how it works. Then you can adapt it to your actual query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNLVbSUTI0MjYxNdNRMDM1MTYy1FGwtDAHMpVidYAqIoDypmAA5vuXZKQWgfWYWhqbmusomJsaW5oaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Netcast Item" = _t, Placeholder = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Netcast Item", type text}, {"Placeholder", type text}}),
#"Convert to Lists" = Table.TransformColumns(#"Changed Type", {"Placeholder", each
List.Transform(Text.Split(_,","), (l)=>Text.Trim(l)), type {text}}),
#"Expanded Placeholder" = Table.ExpandListColumn(#"Convert to Lists", "Placeholder"),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Placeholder",{"Placeholder", "Netcast Item"})
in
#"Reordered Columns"
Source
Results