new Power BI table combining unrelated data

0
votes

I am trying to create a table that is a list of all possible combinations between two tables: products and companies. I'm building a dashboard in Power BI and the data comes from SQL queries. I have the following list structures for the Products table and the Companies table:

input

and my desired output for analysis is:

output

There is nothing that relates the companies to the products, and I'm trying to get a list of all of the products for each company. Can I do this in Power BI? If not, is it possible in SQL (there is nothing to join on)? Thank you for your help!

1
powerbipowerquery

1 Answers

1
votes

You can do this with a 'hacked' join, or a cross join. I prefer the former from a process POV, but cannot speak to speed or efficiency.

Using a Join ( Merge in PQ )

  1. Create a new column 'DummyKey' with a value of 1 on each table.
  2. Merge both tables using your 'DummyKey' columns.
  3. Complete the Join process and choose the columns you want to bring through.

Cross Joins in PowerQuery according to MS

I think this 'cross join' is only technically right and does not provide future flexibility.

Both methods will get you to the same end point, and can be done in SQL or PQ.