Forum Discussion

gotAnAccount's avatar
gotAnAccount
Copper Contributor
Nov 14, 2020

Can't add a Timeline

Hi,

 

Why would I keep getting a date error when I try to add a Timeline in Excel?

 

More specifically, when the date comes from a table that is linked to a Power Query CSV import. I have made sure that the format is the same as the Windows settings, that is the same as Excel default settings, that is the same as the Power Query settings, that is also the same as the field that I'm using and finally even after transforming that column in Power Query so it truly is a date.

 

I create table on the side from scratch with only a 5 lines with a date (of course) and the timeline can be added. (needed to make sure Excel wasn't the issue)

 

What am I missing?

 

Here is the error message:

We can't create a Timeline for this report because it doesn't have a field formatted as Date

 

 

 

thx

excel

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 15, 2020

    gotAnAccount 

    With your permission,

     if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.

    Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

     

    Thank you for your understanding and patience

     

    Nikolino

     

    I know I don't know anything (Socrates)

    • gotAnAccount's avatar
      gotAnAccount
      Copper Contributor
      Nov 15, 2020

      NikolinoDEI can't share the actual spreadsheet for corporate reasons, but I've attached the CSV source file I consume using Power Query. The CSV file is an excerpt of the actual file and it needs some cleanup. Using Power Query, I do some transformations against it so I have a "clean" and structured data source before I use it in a pivot table and Timeline.

       

      You will note that the header of that text file is [id;color;year;month;day] and that the following lines don't actually follow that pattern. After doing some transformations, I end up with a proper Date column but, still can't use Timelines.

       

      With that source file, how can I end up with this structure [id;color;year;month;day;YearMonthDay], where I can use the YearMonthDay field in a Timeline.

       

      For the source file, the line with incomplete data should be transformed to 9999-01-01.

       

      I'm using Windows 10, Excel 2016

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 15, 2020

        gotAnAccount 

        That's hard to say exactly without seeing the data, but I suspect you don't have real data in final result. To convert left 3 columns to date

        it could be code like

            #"Replaced text null to value null" = Table.ReplaceValue(
        #"Promoted Headers",
        "null",
        null,
        Replacer.ReplaceValue,{"year"}
    ),
    #"Added Custom" = Table.AddColumn(
        #"Replaced text null to value null",
        "Date",
        each
            if Text.Length([year]) = 4
            then 
                #date(
                    Number.From([year]),
                    Number.From([month]),
                    Number.From([day])
                )
            else [year]
    ),
    #"Replaced Value" = Table.ReplaceValue(
        #"Added Custom",
        null,
        #date(9999, 1, 1),
        Replacer.ReplaceValue,{"Date"}
    ),
    #"Apply Date Type" = Table.TransformColumnTypes(
        #"Replaced Value",
        {
            {"Date", type date}
        }
    )
in
    #"Apply Date Type"

        Having mixed of texts, dates and numbers in one column it's easy to make error in transformation. After you return transformed data to Excel and create PivotTable it's also could be loss of formatting. But that easy to check with =ISTEXT()

         

Resources