cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SKH19 Resolver I
Resolver I

Unpivot Duplicates into Single Row

Hi,

How can I get this:

Column 1Column 2
A5
A8
B3
B4
C3
C4
D6
D1

Into this:

Column 1Column 2Column 3
A58
B34
C34
D61

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Unpivot Duplicates into Single Row

Hi @SKH19  

please paste the code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJVitWBsCzALCcgyxjOMgGznOFiznAxFyDLDM4yVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column 1"}, {{"partition", each Table.AddIndexColumn(_, "Index", 1,1)}}),
    #"Expanded partition" = Table.ExpandTableColumn(#"Grouped Rows", "partition", {"Column 2", "Index"}, {"Column 2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded partition", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded partition", {{"Index", type text}}, "en-GB")[Index]), "Index", "Column 2")
in
    #"Pivoted Column"

I'm creating a nested index to determine the column number like described here: https://www.youtube.com/watch?v=-3KFZaYImEY

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

View solution in original post

4 REPLIES 4
JoseCruzCat Helper I
Helper I

Re: Unpivot Duplicates into Single Row

Hello!! Un powerquery there is an option called pivote / unpivot rows, this may help!!! Have You tried to use this option???
Super User III
Super User III

Re: Unpivot Duplicates into Single Row

Hi @SKH19  

will there always just be 2 columns or will the number of rows/columns change?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
SKH19 Resolver I
Resolver I

Re: Unpivot Duplicates into Single Row

Hi @ImkeF 

There would be more columns in a scenario where there are more than 2 duplicates, which is fine. For example, if there are 3 G values, it's okay for there to be a fourth column that is mostly null except for row G.

Highlighted
Super User III
Super User III

Re: Unpivot Duplicates into Single Row

Hi @SKH19  

please paste the code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJVitWBsCzALCcgyxjOMgGznOFiznAxFyDLDM4yVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column 1"}, {{"partition", each Table.AddIndexColumn(_, "Index", 1,1)}}),
    #"Expanded partition" = Table.ExpandTableColumn(#"Grouped Rows", "partition", {"Column 2", "Index"}, {"Column 2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded partition", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded partition", {{"Index", type text}}, "en-GB")[Index]), "Index", "Column 2")
in
    #"Pivoted Column"

I'm creating a nested index to determine the column number like described here: https://www.youtube.com/watch?v=-3KFZaYImEY

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors