PBI / Power Query: Create new table B from table A with multiple rows for each table A' row

Question

I have a Date Table (table_A) like the one below:

SALE_DATE
01/10/2022
09/10/2022
12/11/2022
23/11/2022

From that table, I would like to create a new one (table_B) like below:

• First column as distinct month/year table_A[SALE_DATE] duplicated 3 times
• Second column as for each distinct month/year: month/year-2, month/year-1, month/year

MONTH_SALE_DATE	LAST_2_MONTH
10/2022	08/2022
10/2022	09/2022
10/2022	10/2022
11/2022	09/2022
11/2022	10/2022
11/2022	11/2022

The aim is when I have selected a date in november 2022 on my slicer from table_A , to show my matrix' measures with a breakdown for september, october, and november. To achieve this, the idea is to create this table_B with relationship to table_A.

Any help appreciated :)

Answer 1

Here you go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzTQNzIwMlKK1QFyLVG4hkb6hoYIrpExghsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SALE_DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SALE_DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromRecords(
    {[SALE_DATE = [SALE_DATE], LAST_2_MONTH =Date.AddMonths( [SALE_DATE],0) ],
    [SALE_DATE = [SALE_DATE], LAST_2_MONTH =Date.AddMonths( [SALE_DATE],-1) ],
    [SALE_DATE = [SALE_DATE], LAST_2_MONTH =Date.AddMonths( [SALE_DATE],-2) ]

    })
    
    
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SALE_DATE"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"SALE_DATE", "LAST_2_MONTH"}, {"Custom.SALE_DATE", "Custom.LAST_2_MONTH"})
in
    #"Expanded Custom"