Forum Discussion
Create Column to Date an Entire Table
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!
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.
1 Reply
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.