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