Forum Discussion
DAX - Add Index Column by Rows to a Table
Hi nelsonwhyu ,
You should create index column using Power Query. Please check the Power Query forum for such solutions
https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
Incase you want to create a calculated column. Assumption is that there are unique value of metrics and all the format is Metric XY where X is the number (5,10,15, 100 etc)
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thanks Anonymous ,
I also searched through the forum and seems like Power Query is the only way to go. Wonder if anyone have tips regarding this:
Everyday I receive hundreds of files with varying columns into my folder. The way I handle it is to transform the columns into the following format for each file using the PQ Transform File function (unpivoting the columns into {'Attribute','Value'}):
| Classification | Attribute | Value |
| Financials | Metric 5Y | 0.1 |
| Financials | Metric 10Y | 0.2 |
| Financials | Metric 25Y | 0.3 |
| Financials | Spread | 56 |
| Energy | Metric 5Y | 0.2 |
| Energy | Metric 10Y | 0.3 |
| Energy | Metric 25Y | 0.4 |
| Energy | some other metrics | 84 |
Then Power Query combine these file into a single query.
I try generating a separate query by Table.Distinct( 'Table' , "Attribute") , keep only the Attribute Column, and use PowerQuery to add an index column. It does what I am looking for, but the query loop through all my files in folder and take two minutes to generate the step in PQ, and any subsequent steps does the same looping so it's become time consuming to work on that query. It's okay if it takes minutes to actually load the data in, but in development stage in PQ that takes up much time if it's done that way for each step. Any tips is very much appreciated.
As for the DAX formula, thanks a lot I learned a lot from it. But as you said this is for when I have columns that are strictly "Metric XY". Unfortunately those are only subset of the columns that I could receive from files. In fact they can be "KRD 5Y, KRD 10Y,..." and together with "OAS, OAD, etc" So the formula in most cases would return a numeric value in my case.