Forum Discussion
Succinct method to correct date
I have a data set with a column for year and another column for period which, when I receive it, is in the format 'YY-MMM' or 19-Jan. When Power Query automatically changes the fomat to date, it incorrectly uses the current year for every value, and the YY period as the day of the month, so 19-Jan get's converted to 1/19/2022. I have found ways to correct this but feel like it's more steps than necessary, so wondering if there's a succinct way to fix this whereby I essentially let Power Query convert to the incorrect date, then add a new column that replaces the year with the data from the Year column and makes it the end of or beginning of the month. Any suggestions on how to go about this? I've included snips of the formatting. Prior to power query conversionafter conversion w/incorrect dates
Okay after some playing around here's what worked:
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Period", each Text.From(Date.Month([Fiscal Period])) & "/" & Text.From(Date.Day(Date.EndOfMonth([Fiscal Period]))) & "/" & Text.From([Fiscal Year])), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Period", type date}})
3 Replies
I would do this in the PQ editor. Here's generally the code you'd need to add.
#"Added Custom" = Table.AddColumn(Source, "dd_mm_yyyy", each [#"Fiscal Period"] & "-" &[#"Fiscal Year"]), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"dd_mm_yyyy", type date}})That won't really work - it's combining the fiscal period which is in either YY-MMM format as text or M/D/YYYY format as a date, but with the incorrect year, with the correct year column.
Okay after some playing around here's what worked:
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Period", each Text.From(Date.Month([Fiscal Period])) & "/" & Text.From(Date.Day(Date.EndOfMonth([Fiscal Period]))) & "/" & Text.From([Fiscal Year])), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Period", type date}})
