Can't add a Timeline

%3CLINGO-SUB%20id%3D%22lingo-sub-1887304%22%20slang%3D%22en-US%22%3ECan't%20add%20a%20Timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1887304%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20would%20I%20keep%20getting%20a%20date%20error%20when%20I%20try%20to%20add%20a%20Timeline%20in%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMore%20specifically%2C%20when%20the%20date%20comes%20from%20a%20table%20that%20is%20linked%20to%20a%20Power%20Query%20CSV%20import.%20I%20have%20made%20sure%20that%20the%20format%20is%20the%20same%20as%20the%20Windows%20settings%2C%20that%20is%20the%20same%20as%20Excel%20default%20settings%2C%20that%20is%20the%20same%20as%20the%20Power%20Query%20settings%2C%20that%20is%20also%20the%20same%20as%20the%20field%20that%20I'm%20using%20and%20finally%20even%20after%20transforming%20that%20column%20in%20Power%20Query%20so%20it%20truly%20is%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20create%20table%20on%20the%20side%20from%20scratch%20with%20only%20a%205%20lines%20with%20a%20date%20(of%20course)%20and%20the%20timeline%20can%20be%20added.%20(needed%20to%20make%20sure%20Excel%20wasn't%20the%20issue)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20error%20message%3A%3C%2FP%3E%3CP%3EWe%20can't%20create%20a%20Timeline%20for%20this%20report%20because%20it%20doesn't%20have%20a%20field%20formatted%20as%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22gotAnAccount_0-1605379571682.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233596i6236616BEADA16E3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22gotAnAccount_0-1605379571682.png%22%20alt%3D%22gotAnAccount_0-1605379571682.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1887304%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1887997%22%20slang%3D%22de-DE%22%3ESubject%3A%20Can't%20add%20a%20Timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1887997%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869032%22%20target%3D%22_blank%22%3E%40gotAnAccount%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20your%20permission%2C%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eif%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%3C%2FP%3E%3CP%3EKnowledge%20of%20Excel%20version%20and%20the%20operating%20system%20is%20a%20must%20have%20if%20you%20want%20to%20proposing%20a%20reasonable%20solution%20(Example%3A%20office%20version%20e.g.%202016%20or%202019%20or%20365%20web%20or%20365%20pro%2C%20etc.%20and%20your%20operating%20system%20(e.g.%20Win10%20(2004)%2C%20Win%2010%20(1903)%2C%20Mac%2C%20etc.).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888190%22%20slang%3D%22en-US%22%3EBetreff%3A%20Can't%20add%20a%20Timeline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3EI%20can't%20share%20the%20actual%20spreadsheet%20for%20corporate%20reasons%2C%20but%20I've%20attached%20the%20CSV%20source%20file%20I%20consume%20using%20Power%20Query.%20The%20CSV%20file%20is%20an%20excerpt%20of%20the%20actual%20file%20and%20it%20needs%20some%20cleanup.%20Using%20Power%20Query%2C%20I%20do%20some%20transformations%20against%20it%20so%20I%20have%20a%20%22clean%22%20and%20structured%20data%20source%20before%20I%20use%20it%20in%20a%20pivot%20table%20and%20Timeline.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20note%20that%20the%20header%20of%20that%20text%20file%20is%20%5Bid%3Bcolor%3Byear%3Bmonth%3Bday%5D%20and%20that%20the%20following%20lines%20don't%20actually%20follow%20that%20pattern.%20After%20doing%20some%20transformations%2C%20I%20end%20up%20with%20a%20proper%20Date%20column%20but%2C%20still%20can't%20use%20Timelines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20that%20source%20file%2C%20how%20can%20I%20end%20up%20with%20this%20structure%20%5Bid%3Bcolor%3Byear%3Bmonth%3Bday%3BYearMonthDay%5D%2C%20where%20I%20can%20use%20the%20YearMonthDay%20field%20in%20a%20Timeline.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20source%20file%2C%20the%20line%20with%20incomplete%20data%20should%20be%20transformed%20to%209999-01-01.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Windows%2010%2C%20Excel%202016%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

 

gotAnAccount_0-1605379571682.png

 

 

thx

5 Replies
Highlighted

@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)

Highlighted

@NikolinoI 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

Highlighted

@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

image.png

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()

 

Highlighted

@Sergei BaklanI will test your suggestion so thank you in advance. I will say that I haven't given up on my side and... and instead of converting to year 9999, I tested with 2000 and what do you know... i worked immediatly and I applied the same rule for my source file that did not let me add a Timeline and BOOM! and yes, it worked. So... it would seem that my Excel doesn't like it when I try to pass my Timeline a date like 9999.

 

Hope someone will be able to explain why we can't use dates such as 9999 in a Timeline.

 

thank you so very much for your help.

Highlighted

@gotAnAccount 

I don't know why, but it looks like last reliable year to use is 9997

image.png