Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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")
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
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! |
#proudtobeasuperuser |
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
73 | |
67 | |
42 | |
28 | |
20 |