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.
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?
Solved! Go to 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!
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!
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.
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])))
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.
Glad to help!
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |