Date field from SAP BW Query coming in as text

[anthonyloh]

Hello,

I am trying to use the date functionality in Power BI with the date fields coming across from my BW query but they are coming across as text fields and I can't change the format in Power BI to a date. When I highlight the Calendar Day field, the data type options in the toolbar are greyed out. Also, I tried but can't seem to create a new measure that converts the text in Calendar Day to a true date value. I'm still new to DAX but I did find the DATEVALUE function, however I can't seem to refrence the Calandar Day field in my formula. Ultimately, I want my power BI report to show yesterday's date so I need a dynamic date filter.

Has anyone else come across this and how are you handling dates in your Power BI reports?

Thank you,

Anthony

[v-danhe-msft]

Hi @anthonyloh,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @anthonyloh,

Can you tell me which type your data is connected? Direct query or import? If former, I am afraid you could not format your row data, due to the limitation in DirectQuery and SAP Business Warehouse, you could refer to this link:https://docs.microsoft.com/en-us/power-bi/desktop-directquery-sap-bw#additional-modeling-restriction...

And there are many limitations with Direct query in Power BI Desktop, you could refer to this link:

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#implications-of-using-directquer...

I suggest you to modify your data type in your SAP Business Warehouse firstly, then you could use the Power BI to get your data again.

Regards,

Daniel He

[Nivedhitha]

We are using direct query to connect to bw data, the date field extracted as text field. I've gone through the link you mentioned still I need an alternate solution and also I've a doubt can we use Transform column query in advance editor

[pbi_leaner]

Did you get a solution to the problem? We have the same problem!

[Anonymous]

need this to enable incremental refresh  ...any luck ...anybody 

[Anonymous]

i pulled two different date fields in the query and noticed that one does come up as a date and the other one comes up as a text. Both are created as date fields in BW , however the one that does come up as the date field is under the time dimension and the other one is not.  i am not sure if that has anything to do with it. 

---

@Anonymous wrote:

need this to enable incremental refresh  ...any luck ...anybody 

---

[nilimarodrigues]

Hi @Anonymous ,

I am in the same boat wherein I need to use a date based field for incremental refresh option. Unfortunately, for me as well the key date field comes up as TEXT when importing data from SAP BW to Power BI platform.

I can convert the TEXT field to a DATE field but in that case, the dataset is no more supporting "Query Folding" and hence indirectly affects the capability to perform "Incremental refresh".

How did you resolve this issue for your organization?

Thanks,

Nilima

[Nivedhitha]

"Date fields like calendar day, weeks months are not recognized as date fields: for Power BI those are text fields: no filters like today, current year etc. possible" we are using direct query, I've gone through your additional restriction document link but I need alternate solution, can you please help

[anthonyloh]

Hi Daniel,

My report is connected to SAP BW via Direct Query. My date field is already formatted as a date field in BW so there isn't anything more I can do on that end I think. I don't understand why I can't use a formula to create a new field with a formula that converts the text that I am getting from my date field into an actual date value.

Thank you,

Anthony