none
Never happened before to me: the calendar (date table) is not working! RRS feed

  • Question

  • 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 in every date field the locale.

    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 advices!

    Gerónimo

    Saturday, April 22, 2017 2:21 AM

Answers

  • I think that 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. This 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

    • Edited by gerotutu Monday, April 24, 2017 2:49 AM
    • Marked as answer by gerotutu Monday, April 24, 2017 2:49 AM
    Monday, April 24, 2017 2:48 AM

All replies

  • I tried converting every field I got in the form date/time (d/m/yyyy hh:mm:ss) to the simple date (d/m/yyyy) format but the issue remains.

    Saturday, April 22, 2017 2:40 AM
  • I've never worked with a calendar table ranging that far back, so there could lie an issue.

    In Get & Tranform it's pretty easy to dynamically create a calendar, that builds full calendar years only for those times covered in your data tables.

    The following function will prompt you to choose 2 columns from your data tables: The one that contains the earliest date (often the date-column from actuals in finanance models) and the column containing the last date (which is often the date-column from the budget-table). With every data refresh, this calendar-table will be newly created, making sure that all your time intelligence and reports will work properly:

    (ColumnWithEarliestDates as list, ColumnWithLastDates  as list) =>
    #table({"Date"}, List.Transform({Number.From(Date.StartOfYear(List.Min(ColumnWithEarliestDates)))
    								..Number.From(Date.EndOfYear(List.Max(ColumnWithLastDates)))},
    								each {Date.From(_)}))


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, April 22, 2017 4:23 AM
    Moderator
  • My suspicion is that some time field is marked as a date(time) field. When I tried to create a calendar table in Power Pivot automatically from data that included 12/31/1899, it started with 1/1/1899.

    Date numbering starts with 0 at 12/301899 at 12:00 AM (or 0:00), so any time value marked as date is on December 30, 1899.
    This is based on the date numbering in Excel, starting with 1 at 1/1/1900 (so times are on 12/31/1899 or as they say 1/0/1900 which I personally think of as nonsense). However it includes a mistake as 1900 is considered a leap year, so day 60 is 2/29/1900 and day 61 is 3/1/1900, both in Excel and the Power environments.
    Counting back from day 61 (without 2/29/1900) you arrive at 0 on 12/30/1899 0:00.



    • Edited by MarcelBeug Sunday, April 23, 2017 8:42 AM
    Sunday, April 23, 2017 8:41 AM
  • Many many thanks, Imkle! That code seems promising. Let me check it out.

    I found some clues within the birthdays. There are dates before 1900 till 2077.

    Do you know how to replace those "birthyears" in PowerQuery? My idea is to replace dates that are after 2000 and before 1900 with null (or with another year, like 1901).

    Thanks!

    Sunday, April 23, 2017 11:30 PM
  • Monday, April 24, 2017 1:47 AM
  • I think that 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. This 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

    • Edited by gerotutu Monday, April 24, 2017 2:49 AM
    • Marked as answer by gerotutu Monday, April 24, 2017 2:49 AM
    Monday, April 24, 2017 2:48 AM