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

Group columns on pivot table

Hello to every one,

 

I've been trying to group certain columns depending on a value.

 

I have this table:

 

ciervax_0-1618328917873.png

 

 

 

 

PRODUCTFLOWPERIODDECLARANTPARTNERQUANTITYVALUE
13Jan. 2000GermanyAustralia5855800
13Jan. 2000GermanyUK6589800
13Jan. 2000GermanyUSA24512500
13Jan. 2000GermanyCanada32415200
13Jan. 2000GermanyBrazil2153600
13Jan. 2000GermanyArgentina6853666
13Jan. 2000GermanyFrance69823111
13Jan. 2000GermanyItaly523625555
13Jan. 2000GermanyChile255445556
13Jan. 2000GermanyMexico255445558
13Jan. 2000GermanyJapan2125445800

 

 

 

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:

 

ciervax_0-1618328879092.png

 

PARTNERAUSTRALIABRAZILUSACANADAOTHERS
PERIODQTYVALUEQTYVALUEQTYVALUEQTYVALUEQTYVALUE
Jan. 2000585580021536002451250032415200  

 

 

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!

1 ACCEPTED 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?

041605.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

6 REPLIES 6
JW_van_Holst
Resolver IV
Resolver IV

In power query add an column:

Capture.PNG

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?

041605.jpg

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Anonymous
Not applicable

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.

Anonymous
Not applicable

in power query it is not possible, as far as i know, to have two columns with the same name in merged cells (like in excel).

So this is, in my opinion, the one that comes closest to the layout you are looking for.

Prova e facci sapere.

 

 

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