comparing rows values for conditional column in power query

[wingsted93]

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 the user is active, or the date is simply not entered. If the user has two start dates (like ID 1), I want to create the end date for the earliest start date as the latest startdate -1. This I have already accomplished. What i need is a way to compare the ID's across the rows so the above stated logic only applies when same ID occurs more than once. If the ID only occur 1 time and there is no EndDate, it should just put "01-01-9999" to present that the user is active.

Can this be done, and how?

[v-yingjl · Accepted solution]

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.

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyADKVYnVgYhYoYkZwdYaWyEyQnDFQwFjfGEW9CUiRgb45XIOBPhAB5WNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, start = _t, end = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"id", Int64.Type}, {"start", type date}, {"end", type date}}),

    insEndDate=(tab)=>
    let
    start1=List.Skip(tab[start])&{#date(9999,1,2)},
    end1=List.Transform({0..List.Count(start1)-1}, each tab[end]{_}??Date.AddDays(start1{_},-1)),
    tfc=Table.FromColumns(Table.ToColumns(tab)&{end1},Table.ColumnNames(tab)&{"endPop"})
    in tfc,

    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"id"}, {{"all", each insEndDate(_)}}),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"start", "end", "endPop"}, {"start", "end", "endPop"})
in
    #"Tabella all espansa"

PS

@lbendlin I'd like to see the solution with DAX, which I don't know about.

[lbendlin]

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.