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

[fibaek]

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':

I don't need the actual date, just month, quarter, and year. 

When I change the report with the live connection to this dataset from live to direct query the format change to 'date/time':

Without posibilty to change it. Locking the format is probably by design and makes sence. However changing the format on the connection seems to break the automatic date hierarchy which leaves me with a lot of this:

Edit:

It is not the date/time format that breaks the hierarchy. However PBI cannot create a proper date hierarchy on the column after converting it to direct query:

Creating a new date table in the direct query report can solve the issue, but that really should not be necessary.

Furthermore all other hierarchies are broken, and hidden fields and table are no longer hidden. This is also seriously annoying.

Is this a common error or bug? Or has any of you wonderful people been able to solve it?

Thanks!

/Sune

[fibaek]

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

[Pragati11 · Accepted solution]

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