Show missing columns from pivot table

[Koen_D]

Hello everyone,

I have a pivot table that shows the profit from product groups as columns, and all customers as rows. Right now, when I select a customer that has no purchases in product group A, the column with product group A disappears. How can I make it stay visible? I want the layout of the columns to stay the same regardless of filters. I've tried selecting the "Show null values" button, but it doesn't work as I'd hoped.

[Prathamesh_D]

Hey ,

@Koen_D  Can you please share the screenshot of ur pivot table ..?

Thanks

[Koen_D]

Hello @Prathamesh_D ,

Here is an example pivot table as follows:

If now I select Client_B, that has no purchases in SecondGroup, this column will disappear:

 The underlying example data is as follows:

inv:
LOAD * INLINE [
Inv, Client, Item, Price
1, Client_A, I_A, 10
2, Client_A, I_B, 10
3, Client_A, I_B, 20
4, Client_B, I_A, 30
5, Client_C, I_C, 25
6, Client_D, I_B, 10
7, Client_D, I_B, 5
8, Client_D, I_C, 20
9, Client_E, I_A, 25
10, Client_E, I_B, 40
11, Client_A, I_C, 5
];

itm:
LOAD * INLINE [
Item, ItemGroup
I_A, FirstGroup
I_B, FirstGroup
I_C, SecondGroup
];

I'd want to always have all columns stay visible, so the export is always consistent.

I know I could transpose the data in the load script so that the invoice table includes product groups as column names, but my client has many different product groups, which makes this impractical.

[Koen_D]

Anyone has any idea on how to accomplish this?