Forum Discussion
Dynamic header in matrix
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?
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.
2 Replies
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.
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
