Power BI Matrix with Data from Unrelated Columns in Related tables

Question

I have two tables that, by themselves, are unrelated. A special table has been built to make a connection between them. However, even though parts can be connected, others cannot, because no relationship actually exists. I've been asked to create a list of some kind that shows situations where X exists, or where Y exists, or where X and Y both exist.

Here's a sample of made-up data that has a similar structure.

Table X

State	X Event
AZ	10/4/2021
AZ	12/15/2021
CA	8/24/2022
ID	11/14/2022
ID	12/2/2022
ID	12/25/2022
UT	11/4/2021
UT	1/27/2022
UT	6/18/2021
OR	5/12/2021
OR	7/27/2021
WA	5/25/2022
WA	11/2/2022
WA	11/8/2022

Table Y

City	X Event
Bakersfield	4/16/2021
Boise	11/21/2022
Denver	2/16/2021
Eugene	1/20/2022
Fresno	8/14/2021
Las Vegas	2/10/2021
Medford	7/21/2022
Pocatello	7/16/2022
Portland	11/3/2021
Reno	2/19/2022
Sacramento	10/18/2021
Seattle	7/16/2021

The special table adds a State value to Table Y so it can be joined to Table X. Each State has an ID, each City has an ID, and each Event has an ID, but the X Events and the Y Events have different IDs because they are not related.

My output needs to resemble this: Sample Output Matrix

But Power BI won't allow it. For example, there is one X Event date in CA and three Y Event dates in cities in CA. But because the X Event dates have nothing to do with the Y Event dates, they won't line up.

Is there a way to tell Power BI that the X Event column and the Y Event column are unrelated to each other but should appear side-by-side anyway because they are related to the State column?

(Sorry, I don't know why my tables in this post are not formatting correctly. They look fine in the preview but not when published.)

Answer 1

This can be achieved in Power Query Using Merge Queries. First populate State column in Table Y. Then merge two tables.