Short version: How do you import data with .Net DateTime or TimeSpan types to Excel tables via Json and Power Query? I don't get it to work.

More details: I have sparse data (int values logged sparsely in time) that I want to import as tables and graphs in Excel. In my C# code it's represented as Dictionary<DateTime, int>. I export the data to a Json file (using System.Text.Json) and import it to Excel via a Power Query. In Power Query the DateTime ends up as a text column (looks like 0001-07-24T01:05:45 in the preview). I change the type to Date/Time and all seems well (looks like 0001-07-24 01:05:45 in the preview). But when I save and load it to a table, the column is blank. What's going on?

I tried the same thing with TimeSpan instead. Json couldn't serialize TimeSpan so I just used .ToString() before serializing it. In Power Query I converted the values to Duration and when I loaded it to tables it actually worked... although I couldn't get the sorting to work. I could only sort it A->Z, not by time, even though Excel obviously understood that they were times (the graph was sparse as expected).

Sorry if this seems like an Excel / Power Query question, but it's related to the two .Net types and I thought there must be many .Net programmers that have done this before.

CodePudding user response：

If you have 0001-07-24T01:05:45 in powerquery, formatted as date/time it will show you

and load into excel as blank because there is no year 0001 in Excel. You need to set the year to be something else

Maybe something like this adds new column where 0001 becomes 2022

= Table.AddColumn(#"PriorStepName", "Custom", each DateTime.FromText(Text.Replace(Text.From([a]),"0001","2020")),type datetime)

or the replace in place version,

= Table.TransformColumns(#"PriorStepName",{{"ColumnNameHere", each DateTime.FromText(Text.Replace(Text.From(_),"0001","2020")),type datetime}})