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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

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.