Forum Discussion
Append Repeated Values to Multiple Item Rows
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
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)
3 Replies
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)
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)
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"
