Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
4 years ago
Solved

Transpose and create multiple columns

Hello,

 

I'm trying to transpose a table with 5 rows and 1 column and then create multiple columns so we have a transposed version on each row without any duplicates.

 

So what I have is this:

a
b
c
d
e

 

and I'm trying to transform it to the following table:

abcde
bacde
cabde
dabce
eabcd

 

Would anyone know if this is possible in Power BI? I'd really appreciate any help. 

Thank you!

Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi newpbiuser01 ,

    This is pretty straightforward using power query.

    1. Create your data table 

     

    2. Add column and group by all rows. Leave the column as a list of values.


    3) Remove values from the list that are equal to column 1
    For instance, if col1 = a, and elemList = {a,b,c,d,e}, the new column will return {b,c,d,e} only

    4) Merge the original and transformed columns using comma delimiter to get final column as desired



    Sample power query code for reference
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(#"Changed Type",{}, {{"Count", each _, type table}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddColumn([Custom], "Custom", each [Count][Col1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Custom"}, {"elemList"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Col1", "elem"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns", "Custom", each {[elem]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "elemListTransformed", each List.RemoveItems([elemList],[Custom])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"elemListTransformed", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"elemList", "Custom"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns1", "Merged", each Text.Combine({[elem], [elemListTransformed]}, ","), type text)
    in
    #"Inserted Merged Column"

     

    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos!

    Kind regards,

    Rohit

1 Reply

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi newpbiuser01 ,

    This is pretty straightforward using power query.

    1. Create your data table 

     

    2. Add column and group by all rows. Leave the column as a list of values.


    3) Remove values from the list that are equal to column 1
    For instance, if col1 = a, and elemList = {a,b,c,d,e}, the new column will return {b,c,d,e} only

    4) Merge the original and transformed columns using comma delimiter to get final column as desired



    Sample power query code for reference
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyBUymKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(#"Changed Type",{}, {{"Count", each _, type table}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddColumn([Custom], "Custom", each [Count][Col1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Custom"}, {"elemList"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Col1", "elem"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns", "Custom", each {[elem]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "elemListTransformed", each List.RemoveItems([elemList],[Custom])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"elemListTransformed", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"elemList", "Custom"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns1", "Merged", each Text.Combine({[elem], [elemListTransformed]}, ","), type text)
    in
    #"Inserted Merged Column"

     

    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos!

    Kind regards,

    Rohit