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
carlwibe
Frequent Visitor

Some kind of pivoting or how?

Hello,

I am relatively new in the Power Query and M language and need help turning and turning a table I have.

 

I have a table looking like this today...

 

Region   Fruit
1            Mangoes
3            Mangoes
3            Melons
1            Bananas
3            Apples
2            Pears
3            Melons
3            Melons
1            Bananas
1            Apples
1            Mangoes
2            Melons
1            Pears
3            Apples
1            Pear


However, I need it to be displayed like this instead when loaded into an sheet...

 

1                 2            3
Mangoes    Pears     Mangoes
Bananas     Melons       Melons
Apples
Apples
Pears

Pear

 

I apologize if the answer is already in the forum but I in that case did not manage to find it.

Thanks in advance!

 

Regards,

Carl

1 ACCEPTED SOLUTION

Hi @carlwibe ,

if you're in for a Power Query solutin and want to keep the duplicates you can use this approach:

 

 

let
    Source = #table( 
            {"Region", "Fruit"}, 
            List.Zip( { 
                {"1" ,"3" ,"3" ,"1" ,"3" ,"2" ,"3" ,"3" ,"1" ,"1" ,"1" ,"2" ,"1" ,"3" ,"1"},    
                {"Mangoes" ,"Mangoes" ,"Melons" ,"Bananas" ,"Apples" ,"Pears" ,"Melons" ,"Melons" ,"Bananas" ,"Apples" ,"Mangoes" ,"Melons" ,"Pears" ,"Apples" ,"Pear"} } 
                ) 
            ),
    #"Grouped Rows" = Table.Group(Source, {"Region"}, {{"All", each _}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Fruits", each [All][Fruit]),
    #"Removed Other Columns" = Table.Column(#"Added Custom","Fruits"),
    Custom1 = Table.FromColumns(#"Removed Other Columns")
in
    Custom1

 

 

It's a variation of the method I've posted here: https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/ 

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
Anonymous
Not applicable

If you really are new to powerquery you are probably easier off using the power query editor and using the following button:

shosher_1-1599835654825.png

 

Greg_Deckler
Super User
Super User

@carlwibe Try like image below. See PBIX attached below sig, Page 6

Greg_Deckler_0-1599830041816.png

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @carlwibe ,

if you're in for a Power Query solutin and want to keep the duplicates you can use this approach:

 

 

let
    Source = #table( 
            {"Region", "Fruit"}, 
            List.Zip( { 
                {"1" ,"3" ,"3" ,"1" ,"3" ,"2" ,"3" ,"3" ,"1" ,"1" ,"1" ,"2" ,"1" ,"3" ,"1"},    
                {"Mangoes" ,"Mangoes" ,"Melons" ,"Bananas" ,"Apples" ,"Pears" ,"Melons" ,"Melons" ,"Bananas" ,"Apples" ,"Mangoes" ,"Melons" ,"Pears" ,"Apples" ,"Pear"} } 
                ) 
            ),
    #"Grouped Rows" = Table.Group(Source, {"Region"}, {{"All", each _}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Fruits", each [All][Fruit]),
    #"Removed Other Columns" = Table.Column(#"Added Custom","Fruits"),
    Custom1 = Table.FromColumns(#"Removed Other Columns")
in
    Custom1

 

 

It's a variation of the method I've posted here: https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/ 

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

Thanks ImkeF, this was exactly what I was looking for. I will definitely move on now after your educational tip 🙏You deserve a gold medal 🏅  

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.

Top Solution Authors
Top Kudoed Authors