Index by 2 Variables in PowerQuery

[jt1999]

Hello,

I am looking to index a very large data set based on 2 conditons. One condition is based on the date (month specifically, even though it is in short date format for the example provided) and by part number. I have found some close answers, but they do not give me exactly what I need. I know there are ways to do this in DAX, however for my purpose it makes more sense for me to do it in PowerQuery. 

Below is an example of what I am trying to do. 

Date	ID	Index
1/1/2022	ABC	1
2/1/2022	DEF	1
1/1/2022	ABC	2
2/1/2022	DEF	2
3/1/2022	ABC	1
4/1/2022	DEF	1
5/1/2022	ABC	1
1/1/2022	ABC	3

There will be over 100 different "ID"'s and up to 12 months (will not need to carry over to next year). If anyone could point me in the right direction, that would be a great help. 

[jt1999]

Thank you! @v-zhangti

This definitely does help. I suppose the only reason I would prefer to have it in PowerQuery is so I can work with index2 in the transform data tab, but I still should be able to accomplish what I need to with DAX. 

Plus, I am relativley new to PowerBI so I think being able to work in PowerQuery is more comfortable to me as opposed to DAX, but I gotta start somewhere!

[v-zhangti · Accepted solution]

Hi, @jt1999 

I haven't thought of a good way to do this in PowerQuery yet. If you use DAX you can try the following. You need to add a column index in PowerQuery first.

Column:

Index2 =
CALCULATE (
    COUNT ( 'Table'[ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Date] = EARLIER ( 'Table'[Date] )
            && [ID] = EARLIER ( 'Table'[ID] )
            && [Index] <= EARLIER ( 'Table'[Index] )
    )
)

Hope this helps you.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.