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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jamiers
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 = 
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")

 

2 REPLIES 2
m_dekorte
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
  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
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 🙂

 

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

 



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

#proudtobeasuperuser 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors