Forum Discussion

wingsted93's avatar
wingsted93
Frequent Visitor
5 years ago
Solved

comparing rows values for conditional column in power query

I have data like follows: The 'EndDatePopulated' is the column that im trying to create in power query using M. I want the EndDate if it is present. If it is not present it is either because t...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi wingsted93 ,

    Besides using power query like @ Rocco_sprmnt21  mentioned, you can also using DAX to create a calculated column to achieve this:

    EndDatePopulated =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    IF (
        _count >= 2,
        SWITCH (
            TRUE (),
            [StartDate]
                = CALCULATE (
                    MIN ( 'Table'[StartDate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
                ),
                CALCULATE (
                    MAX ( 'Table'[StartDate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
                ) - 1,
            DATE ( 9999, 1, 1 )
        ),
        SWITCH (
            TRUE (),
            ISBLANK ( 'Table'[EndDate] ), DATE ( 9999, 1, 1 ),
            'Table'[EndDate]
        )
    )

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Specifying 9999 as a placeholder year is a rather bad idea.  What will happen is that Power BI will create date hierarchies for all your date fields spanning your minimum data date up to the year 9999.  Those are hundreds of thousands of rows for each column that are eating up a lot of space and performance.

 

If there is no end date, leave it blank and handle it in your logic.

 

To answer your question - yes it can be done in Power Query but in this particular scenario DAX seems to be a better option.