Some kind of pivoting or how?

[carlwibe]

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

[Anonymous]

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

[Greg_Deckler]

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

[ImkeF · 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/ 

[carlwibe]

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 🏅