cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SandyBL Frequent Visitor
Frequent Visitor

New Table based on combination of column values from other tables

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: New Table based on combination of column values from other tables

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.

1.PNG

Please review the attached file.

Best Regards,
Angelia

2 REPLIES 2
MarcelBeug Super Contributor
Super Contributor

Re: New Table based on combination of column values from other tables

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"
Specializing in Power Query Formula Language (M)
v-huizhn-msft Super Contributor
Super Contributor

Re: New Table based on combination of column values from other tables

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.

1.PNG

Please review the attached file.

Best Regards,
Angelia

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 134 members 1,755 guests
Please welcome our newest community members: