Conditional statement open/deleted

[Anonymous]

Hi,

I'm pretty new to Power Query and I need help to implement a new column that shows if a specific OppID is open or deleted if the dataload is different from the refresh time.
Right now I have implemented this step as you can see below:

However, if the specific "OppID" is Open then the column should return "Open" for all rows that that specific OppID. Like this:

[Mariusz · Accepted solution]

Hi @Anonymous 

You can use Group By on OppID, as below.

Add Custom Column with code as below.

And later expan back your table

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

columns.

[AnkitBI · Accepted solution]

You can use below in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc2xDcAwCATAXaiNBP9JwSyW918jREm6YCQa/gQ/p7gMMdccmMe9QN2eZY3X0TgbPxo/a0dGNLXYeB5/0b+jdmaEqP+z6WfTz13/ugA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OppId = _t, DataLoad = _t, LastRefresh = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OppId", Int64.Type}, {"DataLoad", type date}, {"LastRefresh", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"OppId"}, {{"AllRows", each _, type table [OppId=number, DataLoad=date, LastRefresh=date]}}),
    Custom1 = Table.AddColumn(#"Grouped Rows","Status",each if(Table.RowCount(Table.SelectRows([AllRows],each [DataLoad] = [LastRefresh])) > 0) then "Open" else "deleted"),
    #"Expanded AllRows" = Table.ExpandTableColumn(Custom1, "AllRows", {"DataLoad", "LastRefresh"}, {"DataLoad", "LastRefresh"})
in
    #"Expanded AllRows"

In DAX, you can add Conditional Column as below

Status =
IF (
    COUNTROWS (
        FILTER (
            'Table3',
            Table3[DataLoad] = EARLIER ( Table3[LastRefresh] )
                && Table3[OppId] = EARLIER ( Table3[OppId] )
        )
    ) > 0,
    "Open",
    "Deleted"
)

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Mariusz · Accepted solution]

Hi @Anonymous 

You can use Group By on OppID, as below.

Add Custom Column with code as below.

And later expan back your table

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

columns.