#StackBounty: #microsoft-excel #power-query How do I get a cross-join with power query to "remember" values associated with it?

Bounty: 200

Let’s say you have a basic Excel cross-query. You have two lists:

A
B
C

and

X
Y

You cross join them (Cartesian join them), and get the following table:

A - X
A - Y
B - X
B - Y
C - X
C - Y

Now, let’s assign values to each of these numbers:

A - X - 1
A - Y - 2
B - X - 3
B - Y - 4
C - X - 5
C - Y - 6

Now, I want to add a new item to the list – Z

My lists are now

A
B
C

and

X
Y
Z

and my cross join is now:

A - X
A - Y
A - Z
B - X
B - Y
B - Z
C - X
C - Y
C - Z

However, here’s where the issue comes in. After I’ve done this, these are my values:

A - X - 1
A - Y - 2
A - Z - 3
B - X - 4
B - Y - 5
B - Z
C - X
C - Y
C - Z - 6

No no no, this is all wrong.

I’m currently working around this by pasting the value over before I refresh the table, then running an index-match from the new table to the old table to populate the data over. But I imagine that there has to be a better way.

How can I get my cross joins to remember their data, and keep them fixed?

Example expected result:

A - X - 1
A - Y - 2
A - Z
B - X - 3
B - Y - 4
B - Z
C - X - 5
C - Y - 6
C - Z

OS: Windows 10
Version: Excel 365


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.