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.
Hi community,
Hope you all are doing well!
I've a doubt regarding Power BI M-query.
I've created an IF statement via Calculated column, which I'm looking to create in Power Query or Transform Query using M-Query.
Below is the IF statement:
You will need to Add a Custom Column in Power Query.
It will be a combination of an 'if' statement :
https://gorilla.bi/power-query/if-function-in-power-query/
and Text.Right
https://learn.microsoft.com/en-us/powerquery-m/text-end
You may need other Text functions:
https://learn.microsoft.com/en-us/powerquery-m/text-functions
because you may need to change some number fields to text so that you can use them.
You can combine text in power query with '&'.
My advice is start it simply (get something basic that works) and build it step by step adding logic
Hi @HotChilli
I tried using M-query text functions.
below is the snip of the code I tried for creating Fiscal Year:
Please help me out if I'm doing anything wrong in this.
Used Text.From to convert Number to Text. I converted Month and Year datatypes to Text also
Still getting error
below is the error snip:
Please help!
You may try Number.ToText
https://learn.microsoft.com/en-us/powerquery-m/number-totext
Please delete all that I have scored-off and try again:
Still getting same error.
You may try again after making this change. If it still doesn't work, please share the pbix file, if possible
Hi @Padycosmos
I tried but still same error.
Really thank you for your help. I'll try finding other ways too
[Year] is a numeric field, therefore, Text.End([Year],4) will not work, instead please try
Text.End(Number.ToText([Year]),4)
Hi,
I tried doing the way you told
Below is the snip: for few line items it is working and for few it is not
Below is the snip of error
It appears that there is some problem with the data. Could you please change the Data type of the Fiscal Year Column to Whole number and apply the last formula again
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 |
---|---|
113 | |
103 | |
77 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |