"if and if" nested statements in Power Query M

[obera]

Hello Power BI Community,

I know this looks similar to many other questions out there, but I have spent several hours searching and have not found a solution. I'm hoping you can help me out. I have the following formula in Excel that I am trying to convert to Power Query M. It has to be in M and not in DAX.

Data Example - first column is the input and the second column is the expected output (where I have the formula in Excel):

inc_opened_at	Month Opened
3/31/2019 14:48	2019 - 03
4/12/2019 0:50	2019 - 04
4/17/2019 21:57	2019 - 04
4/25/2019 21:38	2019 - 04
4/26/2019 1:53	2019 - 04
4/29/2019 9:41	2019 - 04
4/29/2019 12:28	2019 - 04
4/29/2019 22:36	2019 - 04
4/30/2019 1:07	2019 - 04
4/30/2019 20:45	2019 - 04
5/2/2019 1:26	2019 - 05
5/2/2019 15:25	2019 - 05

Excel Formula:

=IF(H2="","",YEAR(H2))&"-"&IF(H2="","",IF(MONTH(H2)<10,0&MONTH(H2),MONTH(H2)))

I have attempted to convert it to the following in M:

if [inc_opened_at]=null then null else Date.Year([inc_opened_at]) and " - " and if Date.Month([inc_opened_at])<10 then "0" and Date.Month([inc_opened_at]) else Date.Month([inc_opened_at])

However, I am getting a "Token Literal expected" error when putting the above in a Custom Column. I'm pretty sure based on my research that it has to do with the nested if statement I still have, but I can't figure out another way to word the statement. It isn't an "if else if" but an "if and if". Advice?

[BekahLoSurdo · Accepted solution]

Seeing the data will help us know if these are all of the possible issues but some things that may help:

- "and" in Power Query is the Logical Operator; if you are concatenating texts, you'll want "&"

- Before concatenating different data types (dates, numbers, text), you'll want to normalize them so that they match... i.e. Date.ToText() and Number.ToText()

Hope this helps!

[obera]

I have updated my question to include a data set example, thank you, it's my first time posting 🙂

It didn't occur to me that I would have to convert the types, but now that you've said it that seems obvious. I will definitely try that, thank you.

[BekahLoSurdo]

Thanks and Welcome! Your code looks like it will work with the suggested changes:

if [inc_opened_at] = null then null else Number.ToText(Date.Year([inc_opened_at])) & " - " & (if Date.Month([inc_opened_at]) < 10 then "0" & Number.ToText(Date.Month([inc_opened_at])) else Number.ToText(Date.Month([inc_opened_at])))

[obera]

Yes, thank you, that works! I've marked your initial answer as the solution. I really appreciate your help, I was banging my head against my desk.

[BekahLoSurdo]

Glad to help!

[parry2k]

@obera if you put a sample data with desired output and logic, it will help. read this post to get your answer quickly

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490