Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous  

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

 

Imke Feldmann (The BIccountant)

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

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
ImkeF
Super User
Super User

Hi @Anonymous  

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

Imke Feldmann (The BIccountant)

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

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

Anonymous
Not applicable

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.

Hi @Anonymous  

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

 

Imke Feldmann (The BIccountant)

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

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.