Append Repeated Values to Multiple Item Rows

[Hinnantj]

I have two separate, non-related, tables. I want to add all the data from one table to each item on the other table.

Table 1:

Item 
B627
B630 
B642 
B643 
B646 
B647
BA04
BA14
BA15

Table 2:

Month_Year

01/01/2024

02/01/2024

03/01/2024

04/01/2024

05/01/2024

Desired combined table - with both columns and date for each item

Item       Month_Year
B627      01/01/2024
B627      02/01/2024
B627      03/01/2024
B627      04/01/2024
B627      05/01/2024
B630      01/01/2024
B630      02/01/2024
B630      03/01/2024
B630      04/01/2024
B630      05/01/2024
B642      01/01/2024
B642      02/01/2024
B642      03/01/2024
B642      04/01/2024
B642      05/01/2024

[wdx223_Daniel · Accepted solution]

M code

NewStep=#table(Table.ColumnNames(Table1)&Table.ColumnNames(Table2),List.TransformMany(Table.ToRows(Table1),each Table.ToRows(Table2),(x,y)=>x&y))

DAX

NewTable=GENERATE(Table1,Table2)

[Syndicate_Admin]

let
    Source = #"Table 1",
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #"Table 2"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month_Year"}, {"Month_Year"})
in
    #"Expanded Custom"

[spinfuzer]

Add Column =1 to both tables and merge the new custom column (faster performance)

Or Add Custom column =Table2 (slower performance).  You can improve the performance with Table.Buffer, but it will still be slower than a merge.  This means you do something like

buffer =Table.Buffer(Table2),

merge = Table.AddColumn(Source,"merge", each buffer)