Search code examples
mergepowerquerym

All combinations of two table in Powerquery

This is a question about Power Query/ M.

I have two tables: one with unique objects and one table with two columns (year and €). I want to merge the two tables to one table with rows for all the object and years.

How can I do that?

enter image description here

Solution

  • In Table2 add column ... custom column ... using formula:

    = Table1

    Use arrows atop new column to [x] select and expand

    That will give you every combination of Table1 and Table2

    let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Column1.1"})
in #"Expanded Custom"