Date column reconstruction

Hello mmarton 
I am not sure I understood. If you have duplicates you can remove duplicates from the final column.
That is normal if you don't have 2021 in your input to not have it in your final column. If you want to have exactly your result with the format you presented in your first post :

- Introduce a new step (as second last step, between Removed Columns & Added Custom steps) where you replace the value in the column1 : jan by January, feb by February...and so on ;

- Replace the last step with this one : #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year&Month", each [Conditionnal1]&". "&[Column1])

Following on from BA_Pete 's code and suggestion, you can use this extended version of his code to include a column with a date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFWK1YlWKk4tANP5ySVgOi+/DEynpCaDaaBCMzAjKzEPTKelJoHp3MQiMJ1YUATlV0LUleZB6RyIfGk6LotS0W0yp9ymWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [originalColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"originalColumn", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "originalColumn", "dupedColumn"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column", each [dupedColumn], each try Number.From([dupedColumn]) otherwise null,Replacer.ReplaceValue,{"dupedColumn"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "removeRowFlag", each if Text.From([originalColumn]) = Text.From([dupedColumn]) then 1 else 0),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"dupedColumn"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([removeRowFlag] = 0)),
    #"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "newColumn", each Text.Combine({Text.From([dupedColumn], "en-GB"), [originalColumn]}, ". "), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "Custom", each if [originalColumn] = "jan" then 1 else if [originalColumn] = "feb" then 2 else if [originalColumn] = "mar" then 3 else if [originalColumn] = "apr" then 4 else if [originalColumn] = "may" then 5 else if [originalColumn] = "jun" then 6 else if [originalColumn] = "jul" then 7 else if [originalColumn] = "aug" then 8 else if [originalColumn] = "sep" then 9 else if [originalColumn] = "oct" then 10 else if [originalColumn] = "nov" then 11 else if [originalColumn] = "nove" then 11 else 12),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "MonthNum"}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns","nove","nov",Replacer.ReplaceText,{"newColumn"}),
    #"Inserted Date" = Table.AddColumn(#"Replaced Value1", "Date", each Date.From([newColumn]), type date)
in
    #"Inserted Date"

Once you have created this table, you can now create a date table. This date table is customized to what I think are your requirements regarding months (though please check if the turn of years are correct, since January days < 20 are computed as december of the previous year - correct?)

let
    
    MinDataDate = List.Min(#"Original Query"[Date]),
    MaxDataDate = List.Max(#"Original Query"[Date]),
    #"MaxSalesDate1" = Date.AddDays(MaxDataDate, 1),
    DayCount = Duration.Days(Duration.From(MaxSalesDate1 - MinDataDate)),
    Source = List.Dates(MinDataDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    #"Inserted Day" = Table.AddColumn(RenamedColumns, "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Day", "Month", each Date.Month([Date]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Month", "Custom", each if [Day] < 20 then [Month] -1 else [Month]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] = 0 then 12 else [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "NewMonth"}}),
    #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Conditional Column2" = Table.AddColumn(#"Inserted Year", "NewYear", each if [NewMonth] < 12 then [Year] else if[Month] = 1 then [Year] -1 else [Year]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column2",{"Year", "Month"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Removed Columns1", "Custom", each if [NewMonth] = 1 then "Jan" else if [NewMonth] = 2 then "Feb" else if [NewMonth] = 3 then "Mar" else if [NewMonth] = 4 then "Apr" else if [NewMonth] = 5 then "May" else if [NewYear] = 6 then "Jun" else if [NewMonth] = 7 then "July" else if [NewMonth] = 8 then "Aug" else if [NewMonth] = 9 then "Sep" else if [NewMonth] = 10 then "Oct" else if [NewMonth] = 11 then "Nov" else "Dec"),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column3",{{"Custom", "MonthName"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "YearMonth", each [NewYear] *100 + [NewMonth])
in
    #"Added Custom"

You can then create a relationship between the corresponding date fields:

Now use the date table fields in your visuals, measures, slicers etc...

Anonymous  Thank you! 🙂  Perfect output.

BA_Pete PaulDBrown Also thank you for your time and help 🙂