Time Values from Dataflow to Power BI via DirectLake

, ,

Seems simple enough: The table, produced by your Power BI gen2 dataflow, contains a time column. You want to output this table to OneLake, then have Power BI read from it using a DirectLake-powered semantic model.

Sounds easy, but not so fast! When you try to set up the dataflow’s output mappings, columns of type time are not supported for OneLake destinations. Ouch!

So, what do you do?

Try leveraging the fact that Power BI’s time values are, behind the scenes, actually datetime values. For proper “type time” behavior, Power BI expects that the date part of each datetime value is set to Power BI’s epoch date (the date it considers day 0, which is 1899-12-30) and it also needs to know that the column is expected to have time behaviors.

It’s easy to pull this off, at least if you are willing to use a custom (non-default semantic) model:

  1. In your Power Query dataflow, convert the time value to a datetime value. Yup—just a straightforward “change type” to “Date/Time” will do it. The resulting datetime values will automatically reflect a date of 1899-12-30, which is the same as Power BI’s epoch date (exactly the value that’s needed for your purposes—nice!).
  1. When you configure the table’s data destination in Power Query, the “was time now datetime” column should now map just fine to a Date/Time column in OneLake.
  1. Go into the (non-default) semantic model and change the column’s datatype from date/time to time.

There you have it! It took a small hop, skip and jump, but now Power BI treats the column as the time column you intended it to be!

Leave a Reply

Your email address will not be published. Required fields are marked *