Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
If you really are new to powerquery you are probably easier off using the power query editor and using the following button:
@carlwibe Try like image below. See PBIX attached below sig, Page 6
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 🏅
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
53 | |
28 | |
15 | |
14 | |
13 |