Loading OLAP Date column as text in Power BI Desktop

[NuvAnast]

Hello all,

I try to implement an incremental refresh on olap data source in Power BI desktop. It requires to implement filters on a date column and "View native query " option enabled.  The Date column in the DimDate has date data type. But Power BI see it as text. It's not the problem convert this column to date (it appears in format 'MM/dd/YYYY' after conversion), but after this step 'View native query' option is no enabled any more and this prevent set up the incremental refresh.

The details:

1.  In OLAP date column has data type date and format 'MM/dd/YYYY'.

2. Regional settings in Power BI Desktop file is English (United States).

3. Type Detection in Option: Detect column types and headers for unstructed sourses is ON.

Thank in advance for any help, Anastasia.

[Anonymous]

Hi  @NuvAnast  ,

You can try these measures:

1. please ensure the column you would like to set custom filter is Data/Time type.

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

2. Or use DateKey to set incremental refresh

https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-...

This is related content, I hope it helps you

https://community.powerbi.com/t5/Desktop/incremental-refresh-not-available-parameters-not-showing-in...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@NuvAnast , where you have changed the data type.

Check if this is possible in case of a cube, Open power query, Right on the table and open advance editor and if see column list, try to change the data type

refer this video for steps : https://www.youtube.com/watch?v=meUxcbwujl8&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=10

[NuvAnast]

Hi Amitchandak,

Thank you for your answer. 

I can't add the step "Changed Type". This step disabled the "View native query options". I need to changed ometing in cube or Power BI desktop settings....

[amitchandak]

@NuvAnast , Usually for sources like excel, we have a list of columns with data type in the advance editor, I was thinking if you are able to change it there and see if that works. I am not having any cube handy to test that.