How to read BW Date field as DateTime for incremental refresh

[Anonymous]

I have a Date field coming from an SAP BW BEx query, and I'd like to read it as DateTime for driving incremental refresh of my dataflow. Power BI autodetects the key as Date type. I can convert it to DateTime in a subsequent step, but that kills query folding and the key benefit of using incremental refresh for my very large dataflow. If we manufacture a DateTime field in the BEx query, Power BI doesn't recognize it as DateTime.

We've had an accepted development request to drive dataflow incremental refresh on Date fields as well as DateTime, but there's no expected delivery time for this, so I'm looking for alternatives that would let me use incremental refresh and query folding on a BW source based on a Date field.

Has anyone solved this?

[Anonymous]

Hi @Anonymous 

You can refer to the following links:

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#convert-datetime-to-integer

Solved: Incremental refresh using datetime - Microsoft Power BI Community

Best Regards!

Yolo Zhu

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

[Anonymous]

Thank you for the link, Yolo. I tried that, but unfortunately, it appears this only works for dates stored and detected as numeric. The SAP BW connector reads date field level 1 values as type Text and date key properties as type Date. Unless I'm misunderstanding this workaround, it looks like I need to convert the key to DateTime to get it to work out of the box or the Text field to Integer for the workaround, both of which disable query folding.

Am I missing something, or am I painted into a corner until Microsoft delivers the ability to drive incremental refresh based on Date as well as DateTime?

[Anonymous]

Hi Yolo,

I had our SAP BW team create a numeric key figure in yyyymmdd format derived from a date field.  Power BI detects it properly as numeric, but I see no way in the Power BI premium service to enable incremental refresh based on it.

In the service Power Query, I created the RangeStart and RangeEnd parameters, a function to convert them to numeric, and a filter step using them on the numeric date. When I save the dataflow and go to the incremental refresh setup, I'm required to specify a DateTime field.

Does anyone know how to enable incremental refresh with this setup and specify the storage and refresh periods? I'm stuck at the end of every path I've taken trying to enable this.

Thank you!

[Anonymous]

Hi @Anonymous 

Maybe you can add a custom column and use Datetime.Fromtext()  in power query to translate the text column to datetime column,

e.g:

= Table.AddColumn(Source, "Custom", each DateTime.FromText([Column1],[Format="yyyyMMdd", Culture="en-US"]))

You can refer to the following link:

DateTime.FromText - PowerQuery M | Microsoft Learn

Best Regards!

Yolo Zhu

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

[Anonymous]

Thank you, Yolo. Adding a custom column disables query folding, so it doesn't solve my incremental refresh conundrum. I appreciate your suggestions, though.

[kenichisegawa]

Hi @Anonymous 

I'm facing the same issue, have you ever get this use case worked? If so, sharing your workaround would be really appreciated. 

[Anonymous]

Hi Kenichi. Unfortunately, we've not yet found a solution.