New Table based on combination of column values from other tables

[SandyBL]

I want to create a new table in powerbi that joins two columns from different tables and creates combination of all records from each column.  Thanks for the help!

Table 1:

Months

January 2017

February 2017

March 2017

Table 2:

Accounts

1

2

3

New Table

Month                 Account

January 2017       1

February 2017     1

March 2017         1 

January 2017       2

February 2017     2

March 2017         2

January 2017       3

February 2017     3

March 2017         3 

[v-huizhn-msft · Accepted solution]

Hi @SandyBL,

You can use CROSSJOIN function, please click "New Table"(highlighted in yellow background) under Modeling on Home page, type the following formula.

Table = CROSSJOIN(Table1,Table2)

 You will get expected result.



Please review the attached file.

Best Regards,
Angelia

[MarcelBeug]

A solution in Power Query (Query editor - Advanced editor):

let
    Source = Table2,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table1),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Months"}, {"Months"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Months", "Accounts"})
in
    #"Removed Other Columns"