Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to every one,
I've been trying to group certain columns depending on a value.
I have this table:
PRODUCT | FLOW | PERIOD | DECLARANT | PARTNER | QUANTITY | VALUE |
1 | 3 | Jan. 2000 | Germany | Australia | 585 | 5800 |
1 | 3 | Jan. 2000 | Germany | UK | 658 | 9800 |
1 | 3 | Jan. 2000 | Germany | USA | 245 | 12500 |
1 | 3 | Jan. 2000 | Germany | Canada | 324 | 15200 |
1 | 3 | Jan. 2000 | Germany | Brazil | 215 | 3600 |
1 | 3 | Jan. 2000 | Germany | Argentina | 685 | 3666 |
1 | 3 | Jan. 2000 | Germany | France | 698 | 23111 |
1 | 3 | Jan. 2000 | Germany | Italy | 5236 | 25555 |
1 | 3 | Jan. 2000 | Germany | Chile | 2554 | 45556 |
1 | 3 | Jan. 2000 | Germany | Mexico | 2554 | 45558 |
1 | 3 | Jan. 2000 | Germany | Japan | 21254 | 45800 |
I've created a pivot table, all partners/countries are shown, but I want to group certain countries into a "column" inside the pivot table as "others".
This is what I'm trying to get:
PARTNER | AUSTRALIA | BRAZIL | USA | CANADA | OTHERS | |||||
PERIOD | QTY | VALUE | QTY | VALUE | QTY | VALUE | QTY | VALUE | QTY | VALUE |
Jan. 2000 | 585 | 5800 | 215 | 3600 | 245 | 12500 | 324 | 15200 |
The column other should be the sum of the countries which are not shown, those countries will be always the same ones, does not depend on a certain value of qty or value.
Many thanks in advance!
Solved! Go to Solution.
Hi @ciervax
I tried @JW_van_Holst 's solution and create a pivot table like below. The output is the same as your expected result. Is this what you want?
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
In power query add an column:
In your pivot table select _Partner in stead of Partner and it should do the job.
Good luck,
//JW
Thank you for your help.
The problem I see, is the new column _Partner is marking all fields as others, even if the country in partner is not listed as "Australia", "etc"
Then if I select _Partner as a column instead of Partner, is showing only the column Others cause the column is containing all countries.
I don't know why it is not reading the list properly, and all rows are "Others".
Hi @ciervax
I tried @JW_van_Holst 's solution and create a pivot table like below. The output is the same as your expected result. Is this what you want?
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
if you upload a copiable table, could, perhaps, get you a quicker response.
Sure, I've edited the post, I tried the first time to do it, but the second table was not showing properly, cause some fields are combined, I don't know if there is any other option.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.