Date Format from SharePoint List to Power BI

[Heinrich]

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- 

[Heinrich · Accepted solution]

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

[Heinrich]

Hello
I was able to resolve the issue with using version 1.0 of the implementation that gave me the right 

format.

But now the day is wrong on day less. Means instead of 5.1. it shows 4.1.

Regards

Heinrich

[Anonymous]

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

[Heinrich]

Hello
Thank you for the input.
I did that to but Power BI keeps interpreting dd as mm
Regards
Heinrich

[Tahreem24]

@Heinrich , Try creating a new column

Date Column = FORMAT(DateColumn,"DD.MM.YYYY")

[Heinrich]

Hi 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

[Tahreem24]

@Heinrich , Just the DateColumn with your actual date column 
Date Column = FORMAT(YourTableName[test date],"DD.MM.YYYY")

[Heinrich]

Sorry I did that but I don't think I did it right

1. = Table.AddColumn(#"Changed Type", "Test", each [Start Date])

2. = Table.TransformColumnTypes(#"Added Custom",{{"Test", type date}})

3. = FORMAT("MS Teams Enable Teams Federation"[Test],"DD.MM.YYYY")

This gives me an error. How can this be done
Regards
Heinrich

[Tahreem24]

@Heinrich , That formula which I've given is in DAX Calculated column. Attaching screen shot for your reference

[Heinrich]

Thank you for your effort. It worked.
I tried but still a get the wrong data.
Power BI understands SharePoint list as mm.dd.yyyy instead of dd.mm.yyyy
Regards
Heinrich

[Tahreem24]

@Heinrich Could you please only share the Date column into excel? I'll try it at my end.

[Idrissshatila]

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 👍

Follow me on Linkedin

[Heinrich]

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

[Anonymous]

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