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 Everyone,
I just started learning Power BI so please excuse me if this is too simple.
I have a date table listing years 2000-2023. I also have a bunch of other tables each from one of the years (one table for 2020, one for 2021, one for 2022, etc.) that I want to connect to the date table for visualization purposes.
I thought this could be done by adding a column using the query editor to add a column using a =year(yyyy) function, but that yields an error. Is there another way I can connect the tables?
Thanks!
Solved! Go to Solution.
You need a true date - 2022 is not a date. 1/1/2022 is a date. So in your data, create a full date column to connect to the date table. If you are only interested in years, as you seem to be, then just pick an arbitrary date in the year - Jan 1 or Dec 31. Then connect to the date column of the date table. You should do this in Power Query of possible.
#date(2022,12,31) for example.
The reason =year(yyyy) is failing is YEAR() in DAX also needs a date. YEAR(Sales[Date]) will return 2022 if the Date field in the Sales table has 12/31/2022 for the current record.
If that doesn't help, we need more info to know exactly what you are doing and where (measure, calculated column, or a custom column in Power Query)
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou need a true date - 2022 is not a date. 1/1/2022 is a date. So in your data, create a full date column to connect to the date table. If you are only interested in years, as you seem to be, then just pick an arbitrary date in the year - Jan 1 or Dec 31. Then connect to the date column of the date table. You should do this in Power Query of possible.
#date(2022,12,31) for example.
The reason =year(yyyy) is failing is YEAR() in DAX also needs a date. YEAR(Sales[Date]) will return 2022 if the Date field in the Sales table has 12/31/2022 for the current record.
If that doesn't help, we need more info to know exactly what you are doing and where (measure, calculated column, or a custom column in Power Query)
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.