Merge two tables without a relationship

Power Query
1

Hi all,

I am trying to merge two tables without a Unique Identifier between them. The maximum rows are 34K and 17 columns. I first tried and used CrossJoins but it bloated my model to over 230 mb. I then tried to create do merge by creating a dummy Id column in both tables and then merge on the the dummy ID. When I did that, it the load keeps looping at 2.58mb when I try to apply the merge.

Can someone tell me what am doing wrong. Is the number of columns (17) too long that is causing the latency issue?

If not, what other techniques or formulas I can use to make a relationship between two tables that are not unique.
WIP - Campaign.pbix (6.8 MB)

I have attached my WIP file.

Thanks for your help.

2

Can you provide a screenshot sample using MS Excel of what you are trying to accomplish? Sample rows from each table and Sample output table you expecting from the merge to happen would be helpful.

3

Here you go

Sample output - Enterprise DNA.xlsx (1.3 MB)

4

I am not able to view the information available on Campaign File Table on the PBIX file. So I am going to make some assumptions. I am assuming that you have N number of rows in PPD Campaigns and N number of rows Campaign File Columns. So the outcome you are expecting is to have a Merged Table with N rows but with the column headers as specified in your output. If this is the scenario, I would suggest you to use an Index column to add a UID in both tables and then try merging the tables using this UID column. Hope this helps!

5

Thank you. I tried that and the load time was long. I will try something else outside of Power BI to see if it helps with my model until i can find a solution within power bi.

6

Can you add the index column (1, 2, 3) to both tables outside Power BI and then import them and try merging. Also, if possible, can you repost the PBIX file with the Campaign File table but without the Merge query?

7

@ysherriff ,

Just FYI as to why crossjoin blew your model up. Crossjoin performs a Cartesian product, resulting in every combination of rows from table 1 and table 2. Here’s what a 3 x 3 Cartesian product looks like:

image
image1200×913 74.9 KB

A 34,000 x 34,000 Cartesian product will produce 1.156 billion rows. There’s your 230 MB and your long latency…

@pranamg’s approach was spot on. Just wanted to offer additional explanation on crossjoin result.’’

– Brian

1 Like
8

Thank you Brian. Much appreciated

1 Like
9

It is working now pranamg. I appreciate your help

1 Like