Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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}})
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}})
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.
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}})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |