Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Looking for some help with writing a SQL query within PowerQuery. I plan to have at least 8 different saved queries that I will identify with Code variable. I would like to have a Case or other way to pick a WHERE clause based on the value of Code. I have tested this with just the WHERE p.partnumber in ("&SKUList&") and it works but now I am adding more WHERE Statements to choose from.
Here is the code I have so far:
let
Code = List.First(#"tPickOne"[Code]) as text,
SKUList = ("'"& Text.Combine(#"tMultiSKU"[SKU List],"','")&"'"),
ItemTypeName = Text.Combine(#"tItemType"[Item Type Name],"','"),
ItemStatus = Text.Combine(#"tItemStatus"[Item Status],"','"),
Orderability = Text.Combine(#"tOrderability"[Orderability],"','"),
Source = Sql.Database("HOMEDEPOT", "AdHocReporting", [Query="SELECT DISTINCT
p.partnumber as 'Part Number',
p.itemname as 'Description',
s.ItemTypeName as 'Item Type',
p.licensetypename as 'License Type',
p.SuperDivisionName as 'Super Division',
p.SuperDivisionCode as 'Super Division Code',
p.productfamilyname as 'Product Family',
p.productfamilycode as 'Product Family Code',
p.languagename as Language,
p.versionname as Version
FROM dbo.product p (NOLOCK)
CASE "&Code&" WHEN "A" THEN
WHERE p.partnumber in("&SKUList&")
AND p.itemstatusname in('"& ItemStatus & "')
ELSE
WHERE p.itemstatusname in('"& ItemStatus & "')
AND p.OrderabilityFlag in('"& Orderability &"')
END
"]),
Currently I am getting a Token Comma Expected error at the "A" in the CASE Function but I cannot figure out where or if it is needed. Does anyone have a good way to conditionally pick a Where Clause based on a parameter value?
Thanks,
Jared
Hello @jaredj34
as this A should be a text in the query text to SQL you have to write it like ""AA""
Hope this solves all your issues 🙂
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Thanks for the help. The Token Comma Error is now gone but now I am getting an Incorrect syntax near keyword 'CASE' Error.
CASE "&Code&" WHEN ""A"" THEN
WHERE p.partnumber in("&SKUList&")
AND p.itemstatusname in('"& ItemStatus & "')
ELSE
WHERE p.itemstatusname in('"& ItemStatus & "')
AND p.OrderabilityFlag in('"& Orderability &"')
END
I am looking for any way to have a condtional Where statement based on the value of Code. When I get this test to work, I will be adding about 6 more variables and probably about 8 more values for Code. Should a CASE statement like this work? If not, any other ideas?
Thanks,
Jared
Hello @jaredj34
sorry, but I'm not a SQL expert. Consider googling for SQL-case how it has to be written. I was only help you when it came to M-Language.
BR
Jimme
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.