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
let
Source = TableA,
#"Added Custom" = Table.AddColumn(Source, "Lab Name", each LabNames),
#"Expanded Lab Name" = Table.ExpandTableColumn(#"Added Custom", "Lab Name", {"Lab Abbrv"}, {"Lab Abbrv"})
in
#"Expanded Lab Name"
