Forum Discussion
Date Format from SharePoint List to Power BI
Hello
I have a SharePoint List with following settings in the Date field
- Date only and Display format (Standard)
- The date is dd.mm.yyyy
This list is imported into Power BI
Power BI understands it as mm.dd.yyyy and does import only a part of the dates.
I need to change Power BI to read it as dd.mm.yyyy.
I tried following solution
1. Change to locale within Power BI
Do you have any hint
Regards
Heinrich-
Hello
Thank you.
I was able to resolve the case.
I had to change the list settings in SharePoint.
The locale had to be set in the SharePoint List.
Wish you all the best.
Thank you for your support
Heinrich
14 Replies
Hello Heinrich ,
You go to the data tab and click on the date table then on the date column then column tools tab and format the date column as you want.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Hello
Sorry for the late reply.
I did that to and it didn't work.
There is a delta between SharePoint List and Power BI.
What I need to use version 1 for in Power BI
and add an additional Column with a formula who changes UTC to UTC+1/+2 (wintertime).
Regards
Heinrich
HI Heinrich,
You can manually choose on the ‘version 1’ API on the data connector toto getting data, then you can add a custom column to your query table side with DateTimeZone.SwitchZone function to switch the display DateTimeZone:
DateTimeZone.SwitchZone - PowerQuery M | Microsoft Learn
BTW, these datetimezone formats do not support on power bi data model table side so these changes will back to the common UTC format.
If you want to keep these, you need to manually change the datetime values on query editor side to add the timezone offset with raw datetime values.
Regards,
Xiaoxin Sheng
Hello
Thank you for the input.
I did that to but Power BI keeps interpreting dd as mm
Regards
HeinrichHi Thareem24
If the old column is named "test date".
How does the formula has to look like
Date Column = FORMAT(DateColumn,"DD.MM.YYYY")i tried with
Date Column = FORMAT(""test date","DD.MM.YYYY")
but that does not work.
Could you give me a hint
Regards
Heinrich
Hello
I was able to resolve the issue with using version 1.0 of the implementation that gave me the rightformat.
But now the day is wrong on day less. Means instead of 5.1. it shows 4.1.
Regards
Heinrich
Hi Heinrich,
When the day less issue appears? Getting data from SharePoint or M query steps with formatting functions?
Please share more detailed information and description to help us clarify your scenario and test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hello
Thank you.
I was able to resolve the case.
I had to change the list settings in SharePoint.
The locale had to be set in the SharePoint List.
Wish you all the best.
Thank you for your support
Heinrich
