PivotTable error duplicate itemsPivotTable error duplicate items

[PhilippeMuniesa]

Hello (From France)

Below excerpt from a table

Mois_Dsn	Rubrique	Valeur	Nss
1	S21.G00.40.001	01012014	2870556178110
1	S21.G00.40.001	14012021	2970229024223
1	S21.G00.40.001	02012021	2970229024223
2	S21.G00.40.001	01012014	2870556178110
2	S21.G00.40.001	01022021	2970229024223
3	S21.G00.40.001	01012014	2870556178110

I need to make a pivot table with the "Rubrique" in column name and values in rows

The problem is that there are duplicate items, with different values, and in this case the PivotTable generates errors in cells.

(Expression.Error: There were not enough items in the enumeration to complete the operation.
Details:
[List])

the solution would be to calculate a "Rubrique" index ("Mois-Dsn" identical + Indentical "Rubrique" + Identical "Nss")
The first occurrence is equal to 1
The second occurrence at 2 .. Etc.
The goal is to obtain the following table,

Mois_Dsn	Rubrique	Valeur	Nss	indice Rubrique
1	S21.G00.40.001	01012014	2870556178110	1
1	S21.G00.40.001	14012021	2970229024223	1
1	S21.G00.40.001	02012021	2970229024223	2
2	S21.G00.40.001	01012014	2870556178110	1
2	S21.G00.40.001	01022021	2970229024223	2
3	S21.G00.40.001	01012014	2870556178110	1

and merge Heading and Index Heading before doing the pivot table

Mois_Dsn	Nss	S21.G00.40.001_1	S21.G00.40.001_2
1	2870556178110	01012014
1	2970229024223	14012021	02012021
2	2870556178110	01012014
2	2970229024223		01022021
3	2870556178110	01012014

to obtain the following result

But how to get the Topic Index column ...

I have tried several solutions, but I block on this point

Thank you for your valuable help

Philippe

[PhilippeMuniesa]

exactly that i need

Thanks a lot

PH MUNIESA

[PhilippeMuniesa]

Sorry for misformatting sample tables

Attached is the Excel version of this description

https://www.dropbox.com/t/RcKfZ1yXFbO3EqwD 

Thanks in advance for a solution

Philippe Muniesa

[Anonymous · Accepted solution]

Hi @PhilippeMuniesa ,

Please refer to this video for the steps, hope this helps:
Count Occurrence Or Add Index Number To Group Data | Power Query & PowerBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data