Forum Discussion

jt1999's avatar
jt1999
Frequent Visitor
4 years ago
Solved

Index by 2 Variables in PowerQuery

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) a...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

jt1999's avatar
jt1999
Frequent Visitor
4 years ago

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!