Transform data from row to column

[ashkid25]

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, 987654
FX	555555
Others	159357, 753951

Table 2

Paceholder	Netcast Item
123456	Fees
654321	Fees
987654	Fees
555555	FX
159357	Others
753951	Others

[ronrsnfld · Accepted solution]

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