Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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","",


Super User
Super User

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➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn


Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors