Complex Conditional Column

[aarenellsworth]

I'm trying to make a conditional column based on values from two other columns.  If Column B is "yes" than I want Column C to be "yes" whenever Column A has the same value.  Here's an example of what I'm trying to do.

Here's what I have:

Column A	Column B
120569744657	Yes
120569744657
120569744657
120571444599
120571444599
120571444599
120571444599
120571444634
120571444634
120682444573
120682444573	Yes
120682444573
120682444582
120716344638
120718044586
120718044607
120742444631

Here's what I'm trying to do:

Column A	Column B	Column C
120569744657	Yes	Yes
120569744657		Yes
120569744657		Yes
120571444599
120571444599
120571444599
120571444599
120571444634
120571444634
120682444573		Yes
120682444573	Yes	Yes
120682444573		Yes
120682444582
120716344638
120718044586
120718044607
120742444631

Any thoughts on how to do this?  Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Column A]=EARLIER(Table1[Column A])&&Table1[Column B]="Yes"))>=1,"Yes",BLANK())

Hope this helps.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again).

Note - Replaced value step may not be needed for you as I have used that to generate nulls in place of blank.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdC7DcAgDATQXVxTYOMfo0SIMhNkfylARYRCmrRPZ53tUgApimZjVjEIcJwX1LDw1gyZWXL+1TTxh6lTn7X0YtMpm6jTZIatotX4wzz2nC6mcX6DMY0VcVi9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column B"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column A", Int64.Type}, {"Column B", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Temp", each _, type table [Column A=nullable number, Column B=nullable text]}}),
        //Function Start
        fxProcess=(Tbl)=>
        let
            #"Added Custom" = Table.AddColumn(Tbl, "Column C", each if List.Count(List.Select(Tbl[Column B],each _="Yes"))>0 then "Yes" else null)
        in
            #"Added Custom",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column B", "Column C"}, {"Column B", "Column C"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Temp"})
in
    #"Removed Columns"

[aarenellsworth]

It took a lot of massaging, but this solution totally worked!  Thank you very, very much for your help!!!

FYI, here's the final query:

let
    Source = Folder.Files("X:\Files"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type date}, {"Column2", type any}, {"Column3", type any}, {"Column4", type date}, {"Column5", type any}, {"Column6", type any}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", Int64.Type}, {"Column22", type any}, {"Column23", type text}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", Int64.Type}, {"Column28", type any}, {"Column29", type text}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type text}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column2", "Column3", "Column5", "Column6", "Column8", "Column9", "Column10", "Column11", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column21", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column30", "Column31", "Column32", "Column34", "Column35", "Column36", "Column37", "Column39", "Column40"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column4", "Encoutner Date"}, {"Column7", "Patient Name"}, {"Column13", "Medical Service"}, {"Column29", "Primary Resource"}, {"Column33", "Nurse Unit"}, {"Column38", "MRN"}}),
    #"Removed null rows" = Table.SelectRows(#"Renamed Columns", each ([Encoutner Date] <> null)),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed null rows",{{"Encoutner Date", "Encounter Date"}, {"Medical Service", "Appointment Status"}}),
    #"Merged with Description Table" = Table.NestedJoin(#"Renamed Columns2", {"Appointment Status"}, #"Dental UDS Descriptions", {"Description"}, "Dental Description", JoinKind.LeftOuter),
    #"Expanded Dental Description1" = Table.ExpandTableColumn(#"Merged with Description Table", "Dental Description", {"UDS Category", "Appointment Status"}, {"UDS Category", "Appointment Status.1"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Expanded Dental Description1",{{"Appointment Status", "Description"}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns7",{{"Appointment Status.1", "Appointment Status"}}),
    #"Steps to make FIN from MRN and Date" = Table.AddColumn(#"Renamed Columns3", "DateNumber", each [Encounter Date]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Steps to make FIN from MRN and Date",{{"DateNumber", Int64.Type}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type3", "Custom", each [DateNumber]*1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"Custom", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "FIN", each [MRN]&[Custom]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"DateNumber", "Custom"}),
    #"Added columns to match Cerner reports" = Table.AddColumn(#"Removed Columns2", "Facility", each "Dental"),
    #"Added Custom1" = Table.AddColumn(#"Added columns to match Cerner reports", "Attending", each [Primary Resource]),
    #"Removed 'multiple' providers" = Table.SelectRows(#"Added Custom1", each ([Primary Resource] <> "<MULTIPLE>")),
    #"Merged Queries" = Table.NestedJoin(#"Removed 'multiple' providers", {"Primary Resource"}, #"Demographic Attending", {"ATTENDING"}, "Demographic Attending", JoinKind.LeftOuter),
    #"Expanded Demographic Attending" = Table.ExpandTableColumn(#"Merged Queries", "Demographic Attending", {"Attending Provider Type"}, {"Attending Provider Type"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Expanded Demographic Attending",{{"Attending Provider Type", "Medical Service"}}),
    #"Added columns to match Cerner Tables" = Table.AddColumn(#"Renamed Columns5", "Appointment Type", each [Medical Service]),
    #"Added Custom" = Table.AddColumn(#"Added columns to match Cerner Tables", "Encoutner Type", each [Medical Service]),
    #"Renamed Columns6" = Table.RenameColumns(#"Added Custom",{{"Encoutner Type", "Encounter Type"}}),
    #"Removed null Provider" = Table.SelectRows(#"Renamed Columns6", each ([Primary Resource] <> null)),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed null Provider", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filtered Rows" = Table.SelectRows(#"Removed Blank Rows", each [MRN] <> null and [MRN] <> ""),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"MRN", each Text.TrimStart (_, "0"), type text}}),
    #"Steps to HRSA ID" = Table.DuplicateColumn(#"Trimmed Text", "Encounter Date", "Encounter Date - Copy"),
    #"Extracted Year" = Table.TransformColumns(#"Steps to HRSA ID",{{"Encounter Date - Copy", Date.Year, Int64.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Year",{{"Encounter Date - Copy", "Year"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Year", type text}}),
    #"Added Custom5" = Table.AddColumn(#"Changed Type4", "HRSA ID", each [MRN]&[Year]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"Year"}),
    #"Added HRSA Column" = Table.AddColumn(#"Removed Columns1", "Slide Check", each if Text.Contains([Description], "Category A") then "HRSA A" else if Text.Contains([Description], "Category B") then "HRSA B" else if Text.Contains([Description], "Category C") then "HRSA C" else if Text.Contains([Description], "Category D") then "HRSA D" else "No"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added HRSA Column",{"HRSA ID", "Source.Name", "Encounter Date", "Patient Name", "Description", "Primary Resource", "Nurse Unit", "MRN", "UDS Category", "Appointment Status", "FIN", "Facility", "Attending", "Medical Service", "Appointment Type", "Encounter Type", "Slide Check"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Slide Check", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"HRSA ID"}, {{"Temp", each _, type table [HRSA ID=nullable number, Slide Check=nullable text]}}),
        //Function Start
        fxProcess=(Tbl)=>
        let
            #"Added Custom3" = Table.AddColumn(Tbl, "HEALTH_PLAN1", each if List.Count(List.Select(Tbl[Slide Check],each _="HRSA A"))>0 then "HRSA A" else if List.Count(List.Select(Tbl[Slide Check],each _="HRSA B"))>0 then "HRSA B" else if List.Count(List.Select(Tbl[Slide Check],each _="HRSA C"))>0 then "HRSA C" else if List.Count(List.Select(Tbl[Slide Check],each _="HRSA D"))>0 then "HRSA D" else "No")
        in
            #"Added Custom3",
    //Function End
    #"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Custom3", each fxProcess([Temp])),
    #"Expanded Custom3" = Table.ExpandTableColumn(#"Added Custom3", "Custom3", {"HRSA ID", "Source.Name", "Encounter Date", "Patient Name", "Description", "Primary Resource", "Nurse Unit", "MRN", "UDS Category", "Appointment Status", "FIN", "Facility", "Attending", "Medical Service", "Appointment Type", "Encounter Type", "HEALTH_PLAN1"}, {"Slide Check", "Source.Name", "Encoutner Date", "Patient Name", "Description", "Primary Resource", "Nurse Unit", "MRN", "UDS Category", "Appointment Status", "FIN", "Facility", "Attending", "Medical Service", "Appointment Type", "Encounter Type", "HEALTH_PLAN1"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Custom3",{"Temp", "HRSA ID", "Slide Check", "Description", "UDS Category"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns3", each ([Appointment Status] <> "Delete"))
in
    #"Filtered Rows1"

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Column A]=EARLIER(Table1[Column A])&&Table1[Column B]="Yes"))>=1,"Yes",BLANK())

Hope this helps.

[aarenellsworth]

This is great - thank you!  I appreciate the help.  

This solution totally works in DAX.  However, I need to do this in Power Query because I subsequently merge this table with another.  Any suggestions for that?

[VahidDM]

Hi @aarenellsworth 

Try this code to add a new column with DAX:

Column C = 
VAR _Yes =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Column A] ), 'Table'[Column B] = "Yes" )
    )
RETURN
    IF ( _Yes > 0, "Yes", BLANK () )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[aarenellsworth]

This is great - thank you!  I appreciate the help.  

Like Ashish's solution, this solution totally works in DAX.  However, I need to do this in Power Query because I subsequently merge this table with another.  Any suggestions for that?