Writing a simple if statement in Power Query’s M expression language is straightforward. Using an Excel table called Input that contains a single value as the starting point:
The following query shows how to use an if … then … else statement to test whether the value from the table is equal to 5:
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
InputValue = Source{0}[Input],
IfStatement = if InputValue=5
then "The number is five"
else "The number is not five"
in
IfStatement
What about more complex conditional logic? The M language doesn’t include anything like a case statement, but it is possible to write the equivalent of one quite easily.
Here’s an example of a simple case statement:
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
InputValue = Source{0}[Input],
CaseValues = {
{1, "First"},
{2, "Second"},
{3, "Third"},
{4, "Fourth"},
{5, "Fifth"},
{InputValue, "Else condition"}
},
SimpleCase = List.First(List.Select(CaseValues, each _{0}=InputValue)){1}
in
SimpleCase
This works as follows:
- The CaseValues step defines a list containing six items, each of which is itself a list containing a number and some text. The number is the value to compare to the input value, and the text is what will be returned if the number does match the input value.
- The last item in the CaseValues list contains the input value, so this will be returned where the input value matches none of the preceding values
- The SimpleCase step uses List.Select() to filter the list in CaseValues so that only the items in the list where the input value matches the number in the list.
- Since List.Select itself returns a list, this list is then passed to List.First() to get the first item in the list returned by List.Select (there should only be one item in the list in this particular query), and then {1} returns the text from that item. This is the output of the query.
You can write a searched case expression in a very similar way, by declaring functions that return boolean values instead of using numbers as follows:
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
InputValue = Source{0}[Input],
CaseValues = {
{ (x)=>x<10, "Less than 10"},
{ (x)=>x<20, "Less than 20"},
{ (x)=>x<30, "Less than 30"},
{ (x)=>x<40, "Less than 40"},
{ (x)=>x<50, "Less than 50"},
{ (x)=>true, "Else condition"}
},
SimpleCase = List.First(List.Select(CaseValues, each _{0}(InputValue))){1}
in
SimpleCase
In this query the CaseValues step contains a list of lists, where each item in the list consists of list containing a function and a text value. List.Select calls each function and only returns the items where the function returns true, and finally the text from the first item that List.Select returns is the output of the query.
You can download the sample workbook here.
Chris,
That’s very clever! I use stacked else ifs, but that’s not as cool as your solution. For example, as a custom function, stacking else ifs would look like:
let
Source = (x)=>
if x<10 then "Less than 10"
else if x<20 then "Less than 20"
else if x<30 then "Less than 30"
else if x<40 then "Less than 40"
else if x<50 then "Less than 50"
else "Greater than or equal to 50"
in
Source
I’m prepared to admit that this is a bit *too* clever – and that the stacked ifs is probably more maintainable – but this was as much about learning what’s possible in M as solving a real problem.
In fact, I wonder whether the stacked ifs might perform better too…
Performance might be better with stacked else ifs because you avoid multiple function calls. However, my takeaway from your post is, as you said – learning what’s possible in M to solve real problems. The pattern looks like something that can be adapted for other scenarios. It’s ironic when I think that I started using M to fill what I thought were gaps in PQ, and I’ve learned much more than I would have if those gaps weren’t there. I hope to post some of these solutions when I figure out an appropriate outlet.
I just had a case where I had this Chinese textual column (number of CPU cores) I wanted to make numerical. I used the following approach using List.ReplaceMatchingItems() after looking for a way to do a switch statement in M, which seems somewhat similar to your approach, if probably less flexible:
InsertedCustom = Table.ExpandListColumn(Table.AddColumn(ChangedType, “cores”, each List.ReplaceMatchingItems ({[核心数]}, {{“单核心”, 1},{“双核心”, 2},{“四核心”, 4}})), “cores”)
The original column just contained string values while List.ReplaceMatchingItems() required a list, so I first had to cast the column value to a list (-> {[核心数]} ), then cast the result back to a regular non-list value, hence the unfortunate Table.ExpandListColumn(). Too bad it doesn’t just work with non-lists, but as a one-liner it’s fair enough for my purposes here.
Hi Chris! Thank you for your excellent posts about Power Query. I look forward to buy your book when it will be updated.
I also have a question today about some conditional logic which is difficult to solve with my current knowledge of M.
The case is something like:
Brand1 blank blank
blank Category1 blank
blank blank SKU1
Brand2 blank blank
blank Category2 blank
blank blank SKU2
So I need something which can understand if value in the first column isn’t blank and copy value to the same row in column 2 but from second down row in column 2. And then for column 3 also.
Brand1 Category1 SKU1
blank Category1 SKU1
blank blank SKU1
Brand2 Category2 SKU2
blank Category2 SKU2
blank blank SKU2
Then I will be able to use just fill down for all column to get the result:
Brand1 Category1 SKU1
Brand1 Category1 SKU1
Brand1 Category1 SKU1
Brand2 Category2 SKU2
Brand2 Category2 SKU2
Brand2 Category2 SKU2
Won’t the Fill Up/Down buttons do this for you?
If I use Fill Down and then Up here, then I have a problem which you can see below:
Brand1 Category1 SKU1
Brand1 Category1 SKU1
Brand1 Category1 SKU1
Brand2 Category1 SKU1
Brand2 Category2 SKU2
Brand2 Category2 SKU2
First row is ok, but then Category1 dropped to the Brand2 where it shouldn’t be. If I use Fill Up first, then Category2 will be in the Brand1’s row.
Actually I realized that I don’t need it anymore due to specific properties of my data…
But it’s still actual for the future what would you do in that situation?
Sorry for the late reply, I’ve been very busy over the last few days. This is an interesting problem, but I think it is easy to solve. You load the data in as originally shown, then do a fill down on Category and Brand. The important final step, though, is to filter out all but the rows that originally had a SKU on. Here’s the code:
let
Source = Excel.CurrentWorkbook(){[Name=”Table3″]}[Content],
#”Filled Down” = Table.FillDown(Source,{“Brand”}),
#”Filled Down1″ = Table.FillDown(#”Filled Down”,{“Category”}),
#”Filtered Rows” = Table.SelectRows(#”Filled Down1″, each ([SKU] null))
in
#”Filtered Rows”
Thanks Chris! I already did exactly like this. =) Luckily I don’t need rows where SKU is BLANK but what is I’d need them? Miguel found and excellent solution and I still trying to figure out how it works… It’s on the link below.
Also it would be good if you know something to unpivot a table with a few horizontal and vertical headings.
This approach – http://wessexbi.wordpress.com/2014/02/27/unpivot-nested-headings-with-power-query/ – doesn’t work because after Transpose function I still have a few headings.
Can you give me a specific example of what you need?
To simplify, I have a table something like:
Tons Units USD
2010 2011 2012 2010 2011 2012 2010 2011 2012
Brands Categories
And a lot of values inside of it.
So I need:
Brands Categories Measures Period
Sorry. I need:
Brands Categories Period Tons Units USD
Also I lost all my spaces here after click a button “Post Comment”…
So table actually lloks like
————————–Tons—————-Units—————-USD
————————–2010 2011 2012 2010 2011 2012 2010 2011 2012
Brands Categories
I suppose I found something useful considering this issue:
http://www.excelguru.ca/blog/2014/09/24/un-pivoting-with-subcategories-in-power-query/
I will try it and let your know.
Please look at the other two questions.
Ooh, this M language is tough…
Sorry if I’m too bothering you…
Is it possible also in Power Query to do Fill Down function only for columns that *have* text in a first or second row. I don’t know which rows is it. So I need condition like:
1) Take table
2) Find columns that don’t have *null* in a first row or in a second row.
3) Fill down those columns.
I already broke my mind. =( Any ideas how to solve it?
Also I did a misprint in a question. “The sentence I don’t know which rows is it”. should be “I don’t know which columns is it”.
The problem is that I don’t know how to do the second step here.
Theoretically we have a Table.SelectColumns function but how to write it with condition?
Miguel found a solution if you interesting in:
http://www.mrexcel.com/forum/power-bi/822788-power-query-filter-columns-condition.html
Hey guys,
I have struggling with this for ages now.
I want to check if a cell in on each rows contains a value from an Excel table.
Let’s say I have these rows with domains:
http://www.amazon.com
http://www.ebay.com
http://www.shopdirect.com etc.
Then, I want to have a column saying Yes or No if “ebay” or “amazon” or “….” is found on each row. Thse values are stored in standard Excel table outside PowerQuery.
Yes, I can do multiple if Text.Contains… but this is not very user friendly when the list of values to check is expanded frequently.
Any ideas?
Daniel
Ideally, the function should be called “Text.ContainsAnyOf”
Does this post help? http://blog.crossjoin.co.uk/2014/01/27/comparing-columns-in-power-query/
Hmm… Kind of.
But how about the “contains” part? The comparing solution looks for exact match…
Thx!
Hi Chris, wondering if the above can be extended to replicate the SWITCH() function in PP? I have the following code and want to see if I can achieve the same result in PQ, to enable a join to occur on this value with another table.
The SWITCH needs work I know, but here it is as I have it now. Just would like guidance with the syntax on how to achieve the &&, || aspects by showing me a couple of lines.
Cheers
Phil
=
SWITCH (
TRUE (),
Data[BudCntrCode] = “821”, “Sense-Co”,
Data[BudCntrCode] = “713”, “Foundation”,
Data[SchoolCode] = “4640”
|| Data[BudCntrCode] = “779”, “Investment Properies (incl NRAS)”,
Data[FundSrcBudgetCategory] > “BUD_019”
|| Data[FundSource] = “89”, “Net Movement in Restricted Funds”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp2Code] = “IAB”
|| Data[BudGrp2Code] = “IAC”
|| Data[BudGrp3Code] = “IAAA”
|| Data[BudGrp1Code] = “VE” ), “Student Revenue”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] = “IA”
&& Data[BudGrp3Code] “IAAA”
&& Data[BudGrp2Code] “IAB”
&& Data[BudGrp2Code] “IAC” ), “(Block) Government Funding”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] = “IB”
|| Data[BudGrp1Code] = “IC” ), “Contributions to Contracts/Research”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] >= “IE”
&& Data[BudGrp1Code] <= "IZ" ), "Other Revenue",
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] >= “KA”
&& Data[BudGrp1Code] <= "KZ" ), "Salaries",
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( ( Data[BudGrp1Code] >= “LA”
&& Data[BudGrp1Code] <= "TZ" )
&& ( Data[BudGrp2Code] “MAA”
&& Data[BudGrp2Code] “QAA” ) ), “Other”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp2Code] = “MAA”
|| Data[BudGrp2Code] = “QAA” ), “Depreciation”,
( ( Data[BudGrp1Code] >= “IA”
&& Data[BudGrp1Code] <= "TZ" )
&& ( ( Data[BudCntrCode] = "760"
&& Data[FundSource] = "29" )
|| ( Data[FundSrcBudgetCategory] = "BUD_005"
&& Data[FundSource] “29” ) ) ), “Levy Funded Initiatives (LFI)”,
Data[FundSource] = “29”, “Non-Core Funded Initiatives (NFI)”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& Data[BudGrp1Code] = “VA”, “Investment Income”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& Data[BudGrp1Code] = “VC”, “Capital Income”,
“Unknown”
)
Hi Phil, you can just use “and” instead of && and “or” instead of || inside your M code.
Hi Chris, wondering if the above can be extended to replicate the SWITCH() function in PP? I have the following code and want to see if I can achieve the same result in PQ, to enable a join to occur on this value with another table.
The SWITCH needs work I know, but here it is as I have it now. Just would like guidance with the syntax on how to achieve the &&, || aspects by showing me a couple of lines.
Cheers
Phil
=
SWITCH (
TRUE (),
Data[BudCntrCode] = “821”, “Sense-Co”,
Data[BudCntrCode] = “713”, “Foundation”,
Data[SchoolCode] = “4640”
|| Data[BudCntrCode] = “779”, “Investment Properies (incl NRAS)”,
Data[FundSrcBudgetCategory] > “BUD_019”
|| Data[FundSource] = “89”, “Net Movement in Restricted Funds”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp2Code] = “IAB”
|| Data[BudGrp2Code] = “IAC”
|| Data[BudGrp3Code] = “IAAA”
|| Data[BudGrp1Code] = “VE” ), “Student Revenue”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] = “IA”
&& Data[BudGrp3Code] “IAAA”
&& Data[BudGrp2Code] “IAB”
&& Data[BudGrp2Code] “IAC” ), “(Block) Government Funding”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] = “IB”
|| Data[BudGrp1Code] = “IC” ), “Contributions to Contracts/Research”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& ( Data[BudGrp1Code] >= “IE”
&& Data[BudGrp1Code] = “KA”
&& Data[BudGrp1Code] = “LA”
&& Data[BudGrp1Code] = “IA”
&& Data[BudGrp1Code] <= "TZ" )
&& ( ( Data[BudCntrCode] = "760"
&& Data[FundSource] = "29" )
|| ( Data[FundSrcBudgetCategory] = "BUD_005"
&& Data[FundSource] “29” ) ) ), “Levy Funded Initiatives (LFI)”,
Data[FundSource] = “29”, “Non-Core Funded Initiatives (NFI)”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& Data[BudGrp1Code] = “VA”, “Investment Income”,
( Data[FundSrcBudgetCategory] “BUD_005”
&& Data[FundSource] “29”
&& Data[SchoolCode] “4640” )
&& Data[BudGrp1Code] = “VC”, “Capital Income”,
“Unknown”
)
Ignore the post above, realised I can use the if then else approach noted in the early comments to solve my issue. Cheers
Hi,
I am using power query to download data from more than 500 different http links dynamically using power query custom function . All those links I saved in table 1 and name it URL.But now a day facing issue is that sometime any of the particular http links obsolete and when I refresh query it gives me error and stop there.
And I want if any of link not exist than power query just ignore that link and fetch the result from rest http sites.
Looking for expert suggestion here, how can we solve this issue.
This might be worth reading: https://blog.crossjoin.co.uk/2016/08/09/handling-404-not-found-errors-with-web-contents-in-power-query-and-power-bi/ Also, I think try/otherwise does now work with Web.Contents so you could use that too.
Hi Chris,
Thanks a lot , I am able to solve the issue using Try & otherwise.