Create a calculated table from two or more existing tables

[Anonymous]

I have serial numbers and locations in two different tables. I want to compare the datasets by putting the data side-by-side to show for each serial number, which location each dataset shows for each serial number.

Like this:

Serial Number	Dataset 1	Dataset 2
1234	Location 1	Location 1
5678	Location 2	Location 5

What I'd like to do is create a calculated table that unions the serial numbers from both datasets and removes duplicates.  Then I can join that table to both datasets and create a grid that returns all serial numbers from the calculated table with the first location from Dataset 1 and the first location from Dataset 2.

But my formula isn't getting there:

Serial Numbers = DISTINCT(SUMMARIZE('Dataset 1','Dataset 1'[Serial #]) & SUMMARIZE('Dataset 2','Dataset 2'[Serial #]))

Any ideas? Or is there a better way to get the result I'm going for? (A couple of complications... there are instances of duplicated serial numbers in both datasets, and both datasets have serial numbers that aren't in the other dataset.)

[erik_tarnvik · Accepted solution]

Hi @Anonymous,

I agree with @cthurston, this is easier to accomplish in Power Query. If you don't want to go that route, I answered a similar question just an hour or so ago. I am not completely satisfied that I arrived at the most elegant way to accomplish this but in case you want to have a look, check out my response at the end of this thread.

http://community.powerbi.com/t5/Desktop/Connecting-Two-Tables-w-Lookup/td-p/276284/page/2

[Anonymous]

great solution...and quick too.

[Anonymous · Accepted solution]

Actually I found that what I was missing was the UNION function. Creating this calculated table gave me just what I needed:

Serial Numbers = DISTINCT(
    UNION(
        SUMMARIZE(
            'Dataset 1',
            'Dataset 1'[Serial #],
            ),
        SUMMARIZE(
            'Dataset 2',
            'Dataset 2'[Serial #],
            )
        )
    )

Then I was able to join this calculated table to each of the original tables and pull in location, using IFERROR to handle cases with more than one match:

Dateset 1 Location = IFERROR(
    LOOKUPVALUE(
        'Dataset 1'[Location],
        'Dataset 1'[Serial #],
        'Serial Numbers'[Serial #]
        ),
    "Multiple"
    )

[erik_tarnvik · Accepted solution]

Hi @Anonymous,

I agree with @cthurston, this is easier to accomplish in Power Query. If you don't want to go that route, I answered a similar question just an hour or so ago. I am not completely satisfied that I arrived at the most elegant way to accomplish this but in case you want to have a look, check out my response at the end of this thread.

http://community.powerbi.com/t5/Desktop/Connecting-Two-Tables-w-Lookup/td-p/276284/page/2

[cthurston]

Within the query editor you can merge queries as new using an inner join.