Forum Discussion
Date Format
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,
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
- 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.letSource = 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"
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,
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)!
