Forum Discussion

PoweredOut's avatar
PoweredOut
Resolver I
3 years ago
Solved

Conditional Column, check values from other rows in the same table

Hi Experts

 

I would like to create a condional column.

 

I want to compare three columns from the same table and check if the values are the same in other rows.

 

Conditions are if [ID] = [ID](OtherRows) and  [Stage] = [Stage](OtherRows) and [Status] <> [Status](OtherRows), "Declined", "Approved"

 

I basically want to check if the Status is anything but approved based on the condions, it should be declined.

 

I hope this make sense

 

Thanks in advance

IDStageStatusWanted Condional Column
1OpenDeclinedDeclined
1OpenApprovedDeclined
1OpenDeclinedDeclined
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi PoweredOut ,

     

    You can group on [ID] and keep all other rows using the All Rows aggregator. Then just evaluate the nested table for the "Declined" keyword.

    If you want to add more matching dimensions e.g. [Stage] etc., then just include thes with [ID] before you group.

     

    Working example query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM0DUi6pyTmZeakpSrE6KOKOBQVF+WWY4gj1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Stage = _t, Status = _t]),
    groupID = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=nullable text, Stage=nullable text, Status=nullable text, Wanted Condional Column=nullable text]}}),
    addStatusSummary = Table.AddColumn(groupID, "statusSummary", each if List.Contains([data][Status], "Declined") then "Declined" else "Approved"),
    expandDataCols = Table.ExpandTableColumn(addStatusSummary, "data", {"Stage", "Status"}, {"Stage", "Status"})
in
    expandDataCols

     

     

    Example output:

     

    Pete

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi PoweredOut ,

     

    You can group on [ID] and keep all other rows using the All Rows aggregator. Then just evaluate the nested table for the "Declined" keyword.

    If you want to add more matching dimensions e.g. [Stage] etc., then just include thes with [ID] before you group.

     

    Working example query:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM0DUi6pyTmZeakpSrE6KOKOBQVF+WWY4gj1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Stage = _t, Status = _t]),
    groupID = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=nullable text, Stage=nullable text, Status=nullable text, Wanted Condional Column=nullable text]}}),
    addStatusSummary = Table.AddColumn(groupID, "statusSummary", each if List.Contains([data][Status], "Declined") then "Declined" else "Approved"),
    expandDataCols = Table.ExpandTableColumn(addStatusSummary, "data", {"Stage", "Status"}, {"Stage", "Status"})
in
    expandDataCols

     

     

    Example output:

     

    Pete