cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LiJingPing
New Member

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

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.

未命名图片.png

 

未命名图片.png

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

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

transdata.png

 

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

capply.png

 

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

ddd.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User III
Super User III

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

transdata.png

 

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

capply.png

 

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

ddd.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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. 

 

 未命名图片.png

PhilipTreacy
Super User III
Super User III

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

todt.png

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

tod.png

addstep.png

time.png

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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?

 

未命名图片.png

未命名图片.png未命名图片.png

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.