Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to read BW Date field as DateTime for incremental refresh

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?

dataflow
Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.