Forum Discussion
Power Query: Remove day from date month
I have a date column showing the date in that format: DD.MM.YYYY
But I only want to have that in that format MM.YYYY without losing the type date.
How can I do that?
tuncay , Try using below m - code
let
Source = ... , // Your data source here
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateColumn", type date}}),
#"Added Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([DateColumn]), Int64.Type),
#"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateColumn]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Year", "MonthYear", each Text.PadStart(Text.From([Month]), 2, "0") & "." & Text.From([Year]), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DateColumn", "Month", "Year"})
in
#"Removed Columns"
3 Replies
tuncay , Try using below m - code
let
Source = ... , // Your data source here
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateColumn", type date}}),
#"Added Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([DateColumn]), Int64.Type),
#"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateColumn]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Year", "MonthYear", each Text.PadStart(Text.From([Month]), 2, "0") & "." & Text.From([Year]), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DateColumn", "Month", "Year"})
in
#"Removed Columns"MonthYear is formatted still as text. I need it as date. If I convert to date, the days come back..
Converting MM.YYYY to date in Power Query will change the format back to date as essentially MM.YYYY format is not a date - it is a text string. If you want to change the format but keep the date type, format the column in the designer.
