Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Lee_Mather
Regular Visitor

Merging two unrelated tables

Is it possible to merge two unrelated tables together

 

Say for instance I have a Date table, with one column and three rows

 

Date

1-Jan-15

1-Feb-15

1-Mar-15

 

The I also have a Names table, with one column and three rows

 

Name

Rod

Jane

Freddie

 

Is it possible to create a DateName table, , with two columns and nine rows?

 

Date.........Name

1-Jan-15...Rod

1-Feb-15...Rod

1-Mar-15...Rod

1-Jan-15...Jane

1-Feb-15...Jane

1-Mar-15...Jane

1-Jan-15...Freddie

1-Feb-15...Freddie

1-Mar-15...Freddie

 

Thanks in advance

 

Lee

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello @Lee_Mather

 

Yes you can do this in the Query Modeler, but you'll have to modifiy the structure of the tables in order to create a surrogate key" to mege the table.

 

In the table, you add a new column (SK for example) with the value "1" for all the record.

 

You do the same with the other table, and now you can merge the two queries, based on this fake relationship. Don't forget to check the relationship type with "full external" and it should be ok.

 

Sébasttien

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Great solution @Anonymous!

p.s. For other users that may need help on this purpose here is the solution explained:

 

1) Add to the two tables the same "index column" with a fixed index

mergingUnrelated (1).png

 

2) Merge the two tables

mergingUnrelated (2).png

 

 

Anonymous
Not applicable

Hello @Lee_Mather

 

Yes you can do this in the Query Modeler, but you'll have to modifiy the structure of the tables in order to create a surrogate key" to mege the table.

 

In the table, you add a new column (SK for example) with the value "1" for all the record.

 

You do the same with the other table, and now you can merge the two queries, based on this fake relationship. Don't forget to check the relationship type with "full external" and it should be ok.

 

Sébasttien

thanks loads, makes perfect sense

 

ta

 

Lee

Anonymous
Not applicable

5) And you have the result:

mergingUnrelated (7).png

..if you want you can delete the "index colum".

 

#Good Coding To Everyone#

I have a similar issue when I created index columns for two of my tables to which I want to create a relationship, but in my case since I used DirectQuery, It says that the resulted query is not supported with Direct Query.

What would be the best workaround for this? 

Anonymous
Not applicable

3) Select the columns to display

mergingUnrelated (3).pngmergingUnrelated (4).pngmergingUnrelated (5).png

 

4) Select and merge the columns

mergingUnrelated (6).png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.