Very strange! the calendar (date table) is not working

[gerotutu]

Hi experts!

I created 4 queries (get&transform) to upload data from some folders. The ETL processes and the schema are perfectly connected and running. I even added steps to change the locale in each column and the format date/time (d/m/yyyy hh:mm:ss) to just date (d/m/yyyy).

After I had everything checked, I created a "Date Table" but even before connecting it to the data model, I realised about two funny things:

• The function for the day number of the week, weekday() is throwing #ERROR: "An argument of the function has the wrong data type or the result is too large or to small."
• The calendar built starts in 1/1/1899 and 2077.

I connected it and I tried to use it in a pivot table but the calendar is not working. What could be happening?

Thanks for your advice!
Gerónimo

cross-post: https://social.technet.microsoft.co...ar-date-table-is-not-working?forum=powerquery

 

[gerotutu]

UPDATE:
I tried with this DAX formula in Power Pivot:

=DATE(year('Calendar'[Date]);month('Calendar'[Date]);day('Calendar'[Date]))

and dates were converted to "1/1/3799 12:00:00 a.m."

Then I tried with

=int('Calendar'[Date])

and they start with negative numbers (-363)

WHY? I'm completely lost...

 

[gerotutu]

here is a print screen:

 

[citizenbh]

https://msdn.microsoft.com/en-us/library/ee634927.aspx

 

[krrishkrsna]

hi Gerotutu

can u send ur file, so we can try to clear the issue

 

[gerotutu]

I don't know how to share the file as it contains sensitive data =S

 

[gerotutu]

I found some clues within the birthdays.

There are some dates that has wrong years... does anybody know how to replace in PowerQuery dates that are after or before a specific date? My idea is to replace some years with 1901 and see if that's the way I can get the calendar fixed.

Thanks!

 

[MarcelBeug]

Except for the cross post in post #1, this topic is also cross posted on the Power BI Community forum.

 

[gerotutu]

Some momentos ago, I kind of understood what was happening. It was hard to see as there were at least 2 issues mixed:

• Firstly, some birthdates are wrong. I could not get any help in internet on how to replace in PowerQuery just years so I decided to convert the field into text.
• Secondly, I didn't know that the date table doesn't work with time (hours and minutes) so it wasn't matching the PK to any field in the data model. I decided then to transform every date/time format into just date format.

Thanks, everybody for your support and patience. These issues were driving me crazy. Unfortunately, I didn't get all the answers but at least I came up with something that works for me.


Best,
Gerónimo

 

[krrishkrsna]

use left or right function or split option to extract only Year in power query


1. extract only year from Date column
2. replace all 1901 with null through menu Transform > click Replace Values drop down > click Replace Values > in first box type 1901 and in second box type null
3. menu Transform > click Fill drop down > select Fill Down
4. use split option to extract only dd/mm from Date column
5. use either Text.Combine or Merge option combine the two columns


if still not solved this, plz send some example of your data (for which columns you facing issue) not the original data. people can try to solve the issue