Add New Columns into Power Query instead of Power BI Desktop

[jamiers]

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 = 
SWITCH(
    TRUE(),
     '*MergedIDCDMH'[Comply-SA] = "Yes" , "https://pbi.ctri.info/zconditionalicons/SA-Yes.png",
     '*MergedIDCDMH'[Comply-SA] = "No" , "https://pbi.ctri.info/zconditionalicons/SA-No.png",
     '*MergedIDCDMH'[Comply-SA] = "Alert" , "https://pbi.ctri.info/zconditionalicons/SA-Alert.png",
     '*MergedIDCDMH'[Comply-SA] = "Training Not Complete","",
      "100")

[m_dekorte]

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
  let
    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] = "√"
    )
  in
    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

[tackytechtom]

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 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/