Forum Discussion

Liam01's avatar
Liam01
Helper I
3 years ago
Solved

Date Format

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 ...
Need Help
  • AnastasiaS's avatar
    AnastasiaS
    3 years ago

    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,

AnastasiaS's avatar
AnastasiaS
Resolver I
3 years ago

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's avatar
Liam01
Helper I
3 years ago
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's avatar
    AnastasiaS
    Resolver I
    3 years ago

    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's avatar
      Liam01
      Helper I
      3 years ago

      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)!