The calculated Columns（Date type) in Sharepoint lists converts to String Type as imported into PB

[LiJingPing]

Hi,

I am novice of PowerBI and searched the Forums for the solutions for the quesitons but failed. I add a calculated column to calculate date and set the type as Date for this column. It is going well in sharepoint list. The issure is that I imported the sharepoint list to PowerBI. This column type converts to String and can not be calculated in DATEDIF. I raised this question in Sharepoint Forum. It is unsolved and seems it occurs as they repeat the same procedure. It is really appreciated for your solution.

[PhilipTreacy · Accepted solution]

Hi @LiJingPing 

Those screenshots are from the Data Model part of PBI.  You need to make the changes in Power Query.

To open the Power Query editor, click on the Transform data button in the Ribbon

Then you can make the data type changes as I described earlier.

When you are done click on Close & Apply to save the data back into PBI

When you click on your data column header it should now be date data type

Regards

Phil

[LiJingPing]

Hi @PhilipTreacy 

Thank you! I learned how to transform the data. The Tip is the default format form Sharepoint List is with Time Zone. It is transformed to Type of Date correctly as I selected Date/Time/Time Zone. 

 

[PhilipTreacy]

Hi @LiJingPing 

Download sample PBIX file

In PBI click on the Transform data button in the Ribbon, then in Power Query change the column to Date/Time or Date/Time/Timezone depending on your requirements

Then change it to Date - make sure you Add new step if prompted 

Alternatively just try setting it to Date Only in Sharepoint as all the time components are the same.  Then convert that to a date.

Regards

Phil

[LiJingPing]

Hi,Philip

I tried to convert the type in Powerquery of PowerBI. The Error Message Box pops up. I tried to set Date instead of Date and Time in Sharepoint List. It seems PowerBI still convert it to Date/Time. Compared to the Modified Column, it seems the calculated columns Date Formate is yyyy-mm-dd while the default Date column is yyyy/mm/dd. Is it possible the reason for the Error?