cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Awollet33
Frequent Visitor

Turn 2 columns into multiple columns based on a non distinct column

Hi Everyone,

 

Help!

 

Thanks,

Adam

 

data_question.PNG

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @Awollet33 ,

 

Using below M codes to realize it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc27DcAgEAPQXa6muA+QYdAV+e+/QSQ3uCHdk2XZY8guRUxNsgw5YIdPOOALrvANN/iBO/zCG/y/WanTFp1Ov0a/Tr9Bv6tNo02nTkzbzENVMj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", type text}, {"Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"Group1", each _, type table [Account Number=nullable text, Code=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Group1],"Code")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Group1"})
in
    #"Removed Columns"

And you will see:

v-kelly-msft_0-1618470278767.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi  @Awollet33 ,

 

Using below M codes to realize it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc27DcAgEAPQXa6muA+QYdAV+e+/QSQ3uCHdk2XZY8guRUxNsgw5YIdPOOALrvANN/iBO/zCG/y/WanTFp1Ov0a/Tr9Bv6tNo02nTkzbzENVMj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", type text}, {"Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"Group1", each _, type table [Account Number=nullable text, Code=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Group1],"Code")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Group1"})
in
    #"Removed Columns"

And you will see:

v-kelly-msft_0-1618470278767.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

selimovd
Super User
Super User

Hey @Awollet33 ,

 

you can do that with an Unpivot in Power Query:

Pivot and Unpivot with Power BI - RADACAD

Transpose, pivot or unpivot in Power Query? - YouTube

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @selimovd  - Unpivot hasn't been giving me the results I am looking for me. Thank you though. Probably user error 🙃 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!