Dynamic header in matrix

[Anonymous]

I have a table sales:

SalesID. Date. Geography

1.       2020-01.  Italy
1.       2020-01.  France
1.       2020-01.  UK
2.       2020-02.  Italy
2.       2002-02.  Canada
3.       2002-08.  France

Within powerqueryM, I' able to pivot the table. Now the result:

SalesID. Date. Geography1 Geography2.    Geography3
1.    2020-01.   Italy. France. Uk
2.  2020-02.  Italy.  Canada.  Null
3. 2020-08. France

How can I add dynamically the Geography 1, Geography 2.. in a matrix. The number of columns Geography is unlimited and unexpected. For example, if I'll have Geography 4, how to add it dynamically in the matrix header?

[v-easonf-msft]

Hi, @Anonymous 

It seems that @MFelix 's solution can meet your requirement.
If so,  please accept it as  soultion to close this thread.
More people will learn new things here. If you haven't, please feel free to ask.

Best Regards,
Community Support Team _ Eason

[MFelix · Accepted solution]

Hi @Anonymous ,

You do not need to have the table pivoted if you had a column with the Geopgraphy level that you want maknig a count of the rows this will give your the Geography level 1, 2,  and so on.

Using this post you can create an addiotional column with the Index and then geography:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

Now just use that to make it varialbe:

See file attach.