• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power query changes last years dates to current year

C

clarky

New Member
Hi,

I have a power query that gets data from a folder with a number of excel workbooks. It worked well last year but when I refreshed it today all the 2018 dates changed to 2019 in the power query.

The dates in the excel work books are in custom format eg 12/01/2018 and one of the power query steps is to convert that column to date so as to remove the time element that shows up.

Can anyone offer any suggestions as to what is happening please?

Thanks, Mike
 
Not without seeing the source data. How are the dates derived? Via a formula? What is the step you are using in PQ? It should be just change of type.
 
Hi clarky Mike,

As per forum guidelines, follow these
Tips:
  • Use "[ CODE ]" & "[ /CODE ]" tags to embed your M-code => so we can see the power query statement
  • Upload a Sample File to get a quicker response
As suggested by AliGW, most likely you have a formula somewhere that changes the year of the dates. Perhaps something like or with
Code: 
DateTime.LocalNow() as datetime
 
Thanks AliGW and GraH.
Preparing to reply I think I have found the problem.

The dates in the original excel workbook are not formulas but are entered into each cell as 31/12/2018 and appear as 31/12/2018 in the formula bar but appear in the cell as 31-Dec. . So checking what the custom format actually is I found that it is "d-mm" and not "dd/mm/yyyy" that I presumed.

I will change the format to include the year and hopefully that will solve the problem.
The PQ code for changing the type is as follows:

Code: 
[#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns3",{{"Custom.Data.Column1", type date}}),]

thanks, Mike
 
Strange, because PQ should read cell content and not format to my expectations. Knowing that format is like "make-up", and data type is more like "genetic", what you have described should not play a role in PQ converting those 2018-dates into 2019 ones.

So I did a test on my machine and my 2018 dates formatted as "d-mmm" were loaded into PQ as dates in 2018 as I expected. Therefore I believe the root cause of your issue lays elsewhere. But without seeing the full code, it is pure guessing.
 
Hi GraH, it interesting that it works for you without needing to change the format. When I change the format to a date "*14/03/2012" it holds the 2018 year as I would hope.

My full code is below.

Code: 
[let
CharsToRemove = List.Transform({32,46,33..45,47,58..126}, each Character.FromNumber(_)),
CharsToRemove2 = List.Transform({48..57}, each Character.FromNumber(_)),  
Source = Folder.Files("N:\Exchange\Clinic waiting times"),
    #"Filtered Rows4" = Table.SelectRows(Source, each ([Folder Path] = "N:\Exchange\Clinic waiting times\") and ([Extension] = ".xls")),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows4", each [Folder Path] = "N:\Exchange\Clinic waiting times\" and [Extension] = ".xls"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Extension"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Start([Name],Text.PositionOf([Name]," a"))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Clinic"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Name"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Excel.Workbook([Content])),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Content"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Name] = "appointment waiting times 2")),
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Custom.Data", each not Text.Contains([Custom.Data.Column1], ".") or Text.Contains([Custom.Data.Column1], "Waiting")),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each ([Custom.Data.Column1] <> "Waiting times for appointments") and ([Custom.Data.Column2] <> "Drop-in clinics") and ([Custom.Data.Column3] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows3",null,"is null",Replacer.ReplaceValue,{"Custom.Data.Column4"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Replaced Value",{"Custom.Name", "Custom.Data.Column5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns3",{{"Custom.Data.Column1", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Custom.Data.Column1", "Date"}, {"Custom.Data.Column4", "# days"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Days", each Text.Remove([#"# days"],CharsToRemove)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Comments", each Text.Remove([#"# days"],CharsToRemove2)),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom3",{"# days"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns4",{{"Custom.Data.Column3", "type"}, {"Custom.Data.Column2", "clinician"}})
in
    #"Renamed Columns2"]
 
Hi Mike, glad it works for you with the applied workaround, but I don't understand it (yet).

There is a major difference I spot. I tested it using some dates in a table in the same Excel in which I made the PQ, you are reading files from a folder. That might be playing a role. Will try to test this later.
 
Hi GraH, when I get back to work on Monday I will set up some tests as well to see if this is particular to this PQ or not . Thanks so much for your interest.
 
Hi GraH,
You are on to something. When I run a PQ within one of the workbooks with the data by importing "From Table" and change the format to date they remain in the correct year.
If I run the PQ from a new workbook and import "From Workbook" the dates get corrupted.

regards, Mike
 
Thanks for the test and feedback, clarky. It is insightful. 3 years with PQ and never saw that happen. Might have something to do with how those dates are stored in the binary file. I also notice .xls, not .xlsx.
 
That's it GraH :) I hadn't noticed the excel version up to now and so I have just saved one of the original .xls workbooks as a .xlsx and made no other changes and it worked perfectly with the correct dates. I will update them all now.

Thank you very much for your help.
 
You must log in or register to reply here.
Back
Top