Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Add New Columns into Power Query instead of Power BI Desktop

I have two new columns that I am putting into each Power Query table on the Power BI Desktop side and I would rather do this in Power Query.  I was wondering if there would be a way to accomplish this.


Basically, what happens:

(1) Check to see if person has a service level.

(2) If has service level, then check to see if training is current and report Yes or No.

(3) If doesn't have the service level, check training.

(4) If training is completed, ALERT us.

(5) If training not complete, and no service level, then ignore them.


This is the service level check and training check.


Comply-SA = if(ISBLANK('*MergedIDCDMH'[GAP-IDC/DMH.GAP IDC/ DMH/]),if (
    '*MergedIDCDMH'[DCSOVRW.DCS Ovr] = "√" && 
    '*MergedIDCDMH'[DCSCNPBAS.CNP Bas] = "√" &&
    '*MergedIDCDMH'[DCSDEPFUNDTM.Dep Fnd Away] = "√" &&
    '*MergedIDCDMH'[DCSMISCDOVR.Mis Cd Ovr] = "√" &&
    '*MergedIDCDMH'[DMHINTR.DMH Intro] = "√" &&
    '*MergedIDCDMH'[DMHFUND1.DMH Fnd 1] = "√" &&
    '*MergedIDCDMH'[DMHFUND2.DMH Fnd 2] = "√" &&
    '*MergedIDCDMH'[DCSEIW.EIW] = "√"
,  "ALERT", "Training Not Complete"),if (
    '*MergedIDCDMH'[DCSOVRW.DCS Ovr] = "√" && 
    '*MergedIDCDMH'[DCSCNPBAS.CNP Bas] = "√" &&
    '*MergedIDCDMH'[DCSDEPFUNDTM.Dep Fnd Away] = "√" &&
    '*MergedIDCDMH'[DCSMISCDOVR.Mis Cd Ovr] = "√" &&
    '*MergedIDCDMH'[DMHINTR.DMH Intro] = "√" &&
    '*MergedIDCDMH'[DMHFUND1.DMH Fnd 1] = "√" &&
    '*MergedIDCDMH'[DMHFUND2.DMH Fnd 2] = "√" &&
    '*MergedIDCDMH'[DCSEIW.EIW] = "√"
,  "Yes", "No"))



This returns the image that we use in the Power BI report table


GAP √ SA = 
     '*MergedIDCDMH'[Comply-SA] = "Yes" , "",
     '*MergedIDCDMH'[Comply-SA] = "No" , "",
     '*MergedIDCDMH'[Comply-SA] = "Alert" , "",
     '*MergedIDCDMH'[Comply-SA] = "Training Not Complete","",


Solution Sage
Solution Sage

Hi @jamiers 


I'd like to add something to the info already provided by @tackytechtom because I noticed that you've got a duplicate expression inside your IF statement. This is something that can be avoided by storing the result of that expression in another variable by nesting a let-expression for example, that could look like this:


AddComply = Table.AddColumn( PrevStepNameHere, "Comply-SA", each
    a = List.Contains({"", null}, [#"GAP-IDC/DMH.GAP IDC/ DMH/"]),
    b = ( [DCSOVRW.DCS Ovr] = "√"
          and [DCSCNPBAS.CNP Bas] = "√"
          and [DCSDEPFUNDTM.Dep Fnd Away] = "√"
          and [DCSMISCDOVR.Mis Cd Ovr] = "√"
          and [DMHINTR.DMH Intro] = "√"
          and [DMHFUND1.DMH Fnd 1] = "√"
          and [DMHFUND2.DMH Fnd 2] = "√"
          and [DCSEIW.EIW] = "√"
    if a = true
    then (if b = true then "ALERT" else "Training Not Complete")
    else (if b = true then "Yes" else "No")


You'll need to update the initial table reference with the previous step name in your query where it says PrevStepNameHere. Also not knowing your data I've tested [GAP-IDC/DMH.GAP IDC/ DMH/] for both nulls and empty text strings


Amend to your needs.

I hope this is helpful

Super User
Super User

Hi @jamiers ,


This should be possible in Power Query M. It is just a little bit of another syntax. For instance IF(ISBLANK('*MergedIDCDMH'[GAP-IDC/DMH.GAP IDC/ DMH/]))... would translate to if [GAP-IDC/DMH.GAP IDC/ DMH/] = null then....


Here an example that should get you further on how to rewrite the code from DAX:

Solved: Switch statement in power query - Microsoft Power BI Community

(Unfortunately, there is no built-in SWITCH function in Power Query)


Let me know if this helps 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors