POWER QUERY - Basic example -What do I do wrong?

Question

This is quite basic , I seem to have missed the basic idea I guess....

I have two tables:

The first contains both fields:

Table1 :

["Name", ]["Address"].

The second table

Table2 :

["Address"],["Area"}

The Address field is identical in both tables.

I am trying to create a Pivot table that contains "Name","Address","Area".

I am using PowerPivot, I have added both tables to DataModel and created a relationship between both "Address" fields and yet it doesn't seem to work. I tried both directions.

May I have your assistance please?

Thank you so much

What exactly do you mean by "it doesn't seem to work"? Is it throwing an error? What specifically is the problem? — Alexis Olson
Hi and thanks for the comment. It doesn't relate the right "Area" to the right "Name". When I choose "Name" and "Address" fields from the first table and "Area" from the second it gives me a pivot table of name-address and a list of all areas. The connection is probably wrong but I don't see what other option exists. — user118972

QHarr QHarr · Accepted Answer · 2018-04-16T16:08:36

So as a method to achieve your goal and also as a diagnostic you could you powerquery (Free Microsoft add-in for 2013 (results in powerquery tab) and in built in data tab Get and Transform for 2016).

Create 3 queries.

Highlight your first table containing Name and Address. Then go to Powerquery tab(2013) or Data tab (2016) > Get and Transform > From Table

This will load your table up and show editor

Then top left close and load to > connection only AND tick the box for add to data model.

Repeat all of the above for the other table (table 2 with Address, Area) ensuring load as connection and to model.

Then do a new query > combine queries > merge query to combine table 2 with table 1 using an inner join on the address column (highlight the address column in both tables )if you only want exact matches. This becomes part diagnostic as you will see how many rows were matched.

Expand and remove the additional Address column

Close and load connection only and tick add to model. You can now use in a pivottable.

Got to insert pivottable > use an external connection > choose connection

Choose your merge query connection

Follow the last on screen instructions and you have your pivottable.

Otherwise, you may have some useful diagnostic information on the join column.

POWER QUERY - Basic example -What do I do wrong?

1 Answers