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.
Trying to transform a date in the following format '01/01/2017' to year/month. It can be in any of the following formats: 2017-01, 201701, Jan 2017. Some of the values in the date field as null.
Tried the following formula but get an error message that 'Table' is incorrect.
Year-Mon = FORMAT('Table'[Date], "YYYY-MM")
Also tried to concatenate Date.Year and Date.Month but received an error that power bi did not recognize 'concatenate'.
Questions:
1. How to extract year/month
2. Will null values cause an error? Does that need to be built into the formula?
Solved! Go to Solution.
Thank you so much for the quick response. My date type is definately date. Can you confirm what I should be listing as 'table'? I am connecting to an excel file and the tab I am using is called 'Sheet1'. I have no other data files, connections, etc.
If I enter the following formula, I recieve the error below. Is my syntax correcr? =FORMAT(Table[date], "YYYY-MM")
Expression.Error: The name 'FORMAT' wasn't recognized. Make sure it's spelled correctly.
Pictures attached for both ways I have tried this.
Thank you!!
Hi @jleibowitz,
For my test, I create a calculated column using DAX formula in Power BI datamodel, while you create a custom column in Query Editor. DAX and Power Query are different computer language. Please clear them. If you want to create a custom column using Query statement, you should use the following formula.
=Number.ToText(Date.Year([Date]))&"-"&Number.ToText(Date.Month([Date]))
Best Regards,
Angelia
Hi @jleibowitz,
I try to reproduce your scenario and get expected result.
YYYY-MON = FORMAT(Table3[Date],"YYYY-MM") Column = CONCATENATE(Table3[Date].[Year],Table3[Date].[Month])
concatenate is a DAX function, it's weird to recieve the error message. Please confirm your date column is date type
It still workd fine even there is null value, please share your screenshot for further analysis.
Best Regards,
Angelia
Thank you so much for the quick response. My date type is definately date. Can you confirm what I should be listing as 'table'? I am connecting to an excel file and the tab I am using is called 'Sheet1'. I have no other data files, connections, etc.
If I enter the following formula, I recieve the error below. Is my syntax correcr? =FORMAT(Table[date], "YYYY-MM")
Expression.Error: The name 'FORMAT' wasn't recognized. Make sure it's spelled correctly.
Pictures attached for both ways I have tried this.
Thank you!!
Hi @jleibowitz,
For my test, I create a calculated column using DAX formula in Power BI datamodel, while you create a custom column in Query Editor. DAX and Power Query are different computer language. Please clear them. If you want to create a custom column using Query statement, you should use the following formula.
=Number.ToText(Date.Year([Date]))&"-"&Number.ToText(Date.Month([Date]))
Best Regards,
Angelia
Everything worked! Thank you all for the help!
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |