Date Format

[Liam01]

Hello,

I'm having problems with trying to keep the date format of my original Excel sheet that forms the basis of a report.

In Excel it's in the correct UK format - DD/MM/YYYY - but when i'm building the dataflow, it's reverted to MM/DD/YYYY. My browser is Chrome and the Language is set to English (United Kingdom) and both removing the Changed Column Types from the Transform Sample File and formatting the column to Date and UK Locale do nothing.

       

[AnastasiaS]

Hi @Liam01 

Hmm, strange. Can you post please the M query here? that you use to connect to the Sharepoint site.

You can get it by going to the advanced editor:

Thanks

[Liam01]

Hello,

I've pasted as below, i've removed the name of our Sharepoint from the Source line for security but the rest is as it appears.

let

  Source = SharePoint.Files("removed", [ApiVersion = 15]),

  #"Filtered rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "08 - WSP")),

  #"Sorted rows" = Table.Sort(#"Filtered rows", {{"Date modified", Order.Descending}}),

  #"Kept top rows" = Table.FirstN(#"Sorted rows", 1),

  #"Filtered hidden files" = Table.SelectRows(#"Kept top rows", each [Attributes]?[Hidden]? <> true),

  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),

  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Date modified", "Transform file"}),

  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),

  #"Transform columns" = Table.TransformColumnTypes(#"Expanded table column", {{"Order", type text}, {"Service Partner", type text}, {"Service product", type text}, {"P", type text}, {"System status", type text}, {"Sett Rec", type text}, {"Release", type text}, {"Req End", type text}, {"Service Type", type text}, {"Emergency", type text}, {"Cost Type", type text}, {"Description", type text}, {"Sub Description", type text}, {"OpAc", type text}, {"Op User Status", type text}, {"User Status", type text}, {"Postl Code", type text}, {"Teco", type text}, {"WB Workable", type text}}),

  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Order", null}, {"Service Partner", null}, {"Service product", null}, {"P", null}, {"System status", null}, {"Sett Rec", null}, {"Release", null}, {"Req End", null}, {"Service Type", null}, {"Emergency", null}, {"Cost Type", null}, {"Description", null}, {"Sub Description", null}, {"OpAc", null}, {"Op User Status", null}, {"User Status", null}, {"Postl Code", null}, {"Teco", null}, {"WB Workable", null}})

in

  #"Replace errors"

[AnastasiaS]

Hello @Liam01 

I don't see the step where you change the date format for your Date Modified Column.

What you need to do is to first transform your Date Modified to Text type and then add another transform step by choosing "Using locale" -> Date/time -> the desired zone.

Regards,

[Liam01]

Apologies, in the interim, the Date Modified is in the correct UK format in Power Query so I didn't need that step. Although it's now in the US format in Microsoft Teams despite my browser language being English (UK)!

[Liam01]

To add to this, I'm looking at the Date Modified for pulling through the most recent file and that date in Sharepoint is in UK format but in Power Query, it shows as US format. Tried the suggested fix and that doesn't resolve it here.

[AnastasiaS · Accepted solution]

Hello @Liam01 

When in Power Query, have you tried to:

First - change data type to text (normally you'll have only ABC instead of ABC123).

Then - change the format using locale (right clic -> Change type -> Using locale).

Sometimes the intermediate step, transform to text, resolves this problem.

Regards,

[Liam01]

Hi @AnastasiaS 

Thanks - that appears to have worked on one dataflow but not on another one! It's odd - the one it's worked on is pulled from a Table whereas the other one isn't. Not sure if that's anything to do with it, i'll keep looking into it.