Forum Discussion

fibaek's avatar
fibaek
Helper I
5 years ago
Solved

Colum format changes from date to date/time when converting from live to direct query

Hi All, I'm working with the preview function to convert a live connection to a PBI dataset to a direct wuery to the same dataset. The source has a date table with a column of type 'date': ...
General Comment
Need Help
  • Pragati11's avatar
    Pragati11
    5 years ago

    Hi fibaek ,

     

    First thing I will highlight here, Directquery connection comes with some limitations and no support for timeintelligence is one of them. Refer following article for the same:

    https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

     

    If you really need a date hierarchy with this directquery connection, then there is a workaround to achieve this. Basically create a custome hierarchy with different columns. Follow these steps:

    1. Create following columns: [Year], [Quarter], [Month], [Day]
    2. Right click the original Date column and choose 'New Hierarchy'
    3. Drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.

     

    Thanks,

    Pragati

     

fibaek's avatar
fibaek
Helper I
5 years ago

Thanks Pragati!

I missed that little nugget. I thought about rebuilding the hierarchy in the report, but I can certainly live without it. Especially since I evidently can't create it in the dataset and re-use it in the reports with direct query. I honestly don't understand why the hierarchies are lost, hidden fields are shown etc. in the conversion. I would expect those to be inherited. Oh well, guess that's just one of those things 🙂

Thank you for you prompt answer - it is highly appreciated!

/Sune