[MUSIC PLAYING]
Welcome to MSPTDA video number 28--
yes, Microsoft power tools for data analysis.
And in this video, we gotta learn
how to deal with a many-to-many relationship.
And how are we going to deal with it?
In Power BI Desktop and Excel, we're going to use Power Query.
With just a few easy steps, we can build a bridge table.
And this will allow us to connect to tables
in a many-to-many relationship.
Now, last video, we saw a cool array formula
to solve this problem.
But that's not going to help us if we're in an Excel PowerPivot
or Power BI Desktop.
We have the same fundamental problem.
We have two dimension tables.
This is a unique list of authors.
This is a unique list of books.
And if our fact table is giving us
the units that we want to add up.
And book ID is the condition or criteria in each row
to tell us where those units should be used in our formula,
then what are we going to do with this?
We have a unique list here of book ID.
But wait a second.
Two authors?
If our goal is to create these three reports, then total units
by author in a cross tab that includes author,
that's just going to be, flat out, difficult.
Now, if we want to solve this problem
and create these reports using PowerPivot or Power BI Desktop,
we could choose to solve this all with DAX formulas.
But probably the easiest way to deal with this
is with data modeling.
And the NI tool we love for data modeling
is, of course, Power Query.
Now, before we jump into Power Query,
I want to explicitly look at how this
is a many-to-many relationship.
Over in our PDF notes, there's a picture of both tables.
And here's the situation.
Going this direction, if we look at author,
this is the one side.
That is a unique list.
But look at this.
There are many books that each author creates.
But when we have the same two tables going this direction,
this is a unique list of book IDs.
And this one book-- look at this-- has many authors.
So anytime you have a one-to-many this direction
and then a one to many the other direction,
you know you have a many-to-many relationship.
Other examples of many-to-many relationships--
you can definitely have a finished good
that's related on the one side too many ingredients.
But a single ingredient on this side
can be related to many finished goods--
machines to parts, bank accounts to owners,
and many other examples.
Given our three tables, our goal is
to create these three reports--
author, with the total units for each author, book title
total units, and then this crosstab
report that shows book title, author,
and the totals for each column and correctly for each row.
Even though there are duplicates here,
we only have a total of 228.
Now we're going to start over here in Excel.
And we'll jump over to Power BI Desktop later.
Here's the file if you want to download it and follow along.
On the sheet, PQ Bridge Table, here are our tables.
I've already added dBooks and fSales to the data model--
PowerPivot ribbon tab, manage data model.
Here, in diagram view, here's fSales.
There's already a measure.
And look at this.
This is the book ID, the one side, one-to-many relationship.
This model has no problem.
One to many?
If I want book title and total units, I can do that.
We can click Pivot Table, New Sheet, click OK.
Name this dBooks.
Drag book title down to rows.
Drag total units down to values, and we get what we want.
Each one of the book titles flows
in as a condition or criteria.
Since there's a one-to-many relationship,
it accurately filters the fact table to give us
the total units for each book.
Back over on PQ Bridge, dAuthors, PowerPivot--
add to data model.
There's the dAuthors table.
Alt-Tab, back over on our sheet, I'm
going to copy this pivot table and sheet.
Paste below-- Control-V. I'm going
to get rid of book title and check authors.
The yellow here is trying to be polite.
We're not going to touch it.
It does tell us the truth.
There is no relationship.
So of course, when that happens, we get the grand total
all the way around.
The way we're going to solve this
is with a bridge table between authors and dBooks.
From dBooks, we'll have a list of all the book IDs repeated
for each one of the authors.
Then, with the bridge table, we can
build our one-to-many relationship,
one-to-many relationship, where the many side for both tables
is listed in the bridge table.
Alt-Tab.
Back over on PQ Bridge, if we could take dBooks and transform
this record so that we have DA22 book ID listed twice,
and then repeat this once, and then on the second row,
we'd have exactly what we want, a unique list
of all the combinations of book IDs and author IDs.
So we select a single cell, data ribbon tab, get and transform.
We need to bring it into the Power Query Editor
with From table.
Query name is listed on the left and the right.
We're going to change this--
something like bridge, book ID, author ID.
Now the columns we need our book ID and the two author IDs.
So holding Control, I click, click, right-click,
remove other columns.
Now, for this record here, we're starting with that item.
And I need to repeat this twice.
So I select book ID column, right-click.
And the feature we want is unpivot other columns.
And just like magic, we have a repeat and our author
for that book on 2 line.
Now, we used, up in the formula bar, table.unpivot.
And I want to name this column right here.
So I'm going to double-click.
And this is Author ID and enter.
Now I can select Attribute, right-click, Remove.
That is our bridge table.
Now I can close and load, close and load 2.
I want only create a connection.
That's just for the Power Query transformation.
Now I want to load it to the data model.
Click OK.
We can see our loaded query here up
in the data ribbon tab, data tools.
I'm going to select Manage Data Model.
We can see our bridge table, diagram view.
And there's our bridge.
And now, what do we do?
We take AuthorID.
This is the one side over to the many side.
There's our one to many.
Remember, if we select author in a pivot table or a Power BI
visual, that filter flows across and filters the bridge table.
And what about book ID?
Drag that to BookID.
Now there's a one-to-many relationship.
Notice the many, many on the bridge table side.
Now, how is this going to help?
Let's go look at our pivot table and remind ourselves
what the trouble is.
For this row, in the pivot table Bill Szysz.
It tries to flow in, but it cannot get to that fact table
measure.
That condition is coming from the author table.
So, so far, the author Bill Szysz
is coming across and filtering this table.
Now, normally, at this point in the video, I put up visuals.
But here's a cool trick.
Let's go over to Data view.
Here are our authors.
Here's what happens in the pivot table.
The row condition is only Bill Szysz.
So that flows across.
What's happening behind the scenes is,
the filter hits this bridge table,
and only Bill Szysz is showing.
And just like that, we have the three book IDs that we need.
But what's the problem?
That's the problem right there.
A filter cannot automatically go from the many side to the one
side.
By default, filters always flow from the one side
to the many side.
Well, at this point, we have three options.
We can use a full table filter.
That pushes any filter from this table
across to the other one side.
We can use the CROSSFILTER function.
And CROSSFILTER will ask us the question, hey, which direction
do you want the filter to flow?
And we'll say both.
Or we can jump over to Power BI Desktop
and simply edit the filter so that it's
a bi-directional filter.
Now, I want to start by showing you the bidrectional filter.
And you can download this Power BI Desktop start file.
But here's the thing.
We need to create the bridge table before we can simply
change the relationship.
Now, we already did this over in Excel.
But I do want to show you, over here in Power BI Desktop,
creating the bridge table.
And it's easy.
We've already imported these tables in Power BI Desktop
home, external data.
There it is.
I can click Edit Queries.
This is Power Query and Power BI Desktop.
And over here, since I have imported all of these
from the Excel file we were just working
in, I come to books, right-click, Reference.
That means this is looking to there as a reference.
It's not going to re-import the table.
We want to name this and enter, hold control, click, click,
right-click, remove other columns,
right-click, unpivot other columns.
Up in the Formula bar, double-click, author ID,
enter, attribute, right-click, Remove.
That's our bridge table.
And when I click Close, it closes the Power Query Editor.
When I click Apply, it loads it to the data model.
And look at that.
It not only imported it, it already
created the relationships.
Now, I already created the reports here.
And we can see the same errors we had over in Excel.
Those are the grand total.
And look at this.
The crosstab lists the numbers everywhere.
In relationships, here's how easy it is.
Right-click, Properties.
Instead of single, I'm going to say crossfilter direction,
both, click OK.
There it is, the icon for both.
Over in Report view, that is working, and so is this.
Now, how did our model calculate 720 total units for Bill Szysz?
Well, by selecting the condition, Bill Szysz, author,
this table is filtered down to a single record.
By default, that filter goes from the one to the many side.
So now the bridge table has the three books for Bill Szysz.
Now that we have a bidirectional filter,
those three books flow across.
Now dBooks is filtered down to just those three books.
And then the default one to many, those three books
are passed over to fSales.
We see all the units for those books,
and the total units measure can sum to get 720.
That's the magic of bridge tables
and a many-to-many relationship.
Now one thing about bi-directional filters
is, it could cause ambiguity in the model.
That means, if some of the tables
were linked to other tables in two different directions,
then the model might not know where to send the filter when
a filter occurs.
But for us, it's all in one direction,
so there is no ambiguity.
So that's method number one.
Now we're going to jump over to Excel
and do table filter and crossfilter.
You could do that over here also.
If you did have ambiguity in the model,
and your tables weren't connected
in a single direction, then you could use these other two
methods.
Now, in Excel PowerPivot, we only
have this one-to-many relationship.
So now we're going to go over to Data view.
fSales in the measure grid, total units, CROSSFILTER,
colon, equal sign-- and what's the name and the function that
can change the filter context?
The calculate function.
Expression, square bracket-- there is our total units tab,
comma.
The filter-- this is where we put CROSSFILTER.
There's the bridge table, book ID, tab, comma.
The column that we want to receive this many-to-one
filter--
it's book ID in the book table, tab, comma.
And then, the third argument, we're
going to say we want it to go both directions.
So now what we're doing is changing how the filter
works for this formula.
Now, one thing important is that, over in Power BI, when
we switch that filter to bi-directional, that will
work for any formula we create.
But over here, if we want multiple formulas
to be able to go from the many to the one side,
we have to add this cross filter to each formula--
close parentheses, CROSSFILTER sitting in calculate,
close parentheses, and enter.
Add some number formatting.
Back over to our pivot table, there's
total units CROSSFILTER.
When I drag it down, now, it has no problem.
Now, let's go to the second method.
I'm going to call this one Total Units Table Filter.
There's our calculate, our measure, comma.
And I'm simply going to put the full bridge table.
That bridge table, for Bill Szysz is in the pivot table,
has those three rows filtered.
By putting this entire table into filter,
that sends those three filters over to the fact table.
Now, if we look at the expanded table diagram,
here's our data model.
And we're trying to get the filter from the bridge table
over to the fact table.
Well, internally, inside the engine,
here's our bridge table.
These are the native columns, book ID and author ID.
But any table also has expanded columns,
columns that flow from the one side to this table's many side.
So of course, because this is one to many, one to many,
the bridge table has all the columns
from authors and from books.
So that means, when we select a particular author here,
it filters this table including the book ID column.
So that's why, when we put the full table,
it will filter the fSales.
There it is.
I added some number formatting.
We can already see there's a difference.
Over here in the pivot table, I drag table filter down.
And there we go.
We get the correct book totals for each author.
Now, what's going on with the $100 difference?
Well, if we look up at this pivot table right here,
we know from our study earlier on this class,
video number 15, when we see a blank,
and we're pulling from a dimension table, some field,
that blank means there's an unmatched
item in the relationship.
There it is in our fact table.
RRR, that does not exist as a book ID.
Now, the pivot table is being polite.
It doesn't know what that is.
But it wants to include in the total.
But what's going on here?
Well, when we used crossfilter, that just
changed the direction of the filter.
So in the grand total cell, of course, the entire fact table
has no filters.
So it shows every single record, including the unmatched $100.
But over here, we used a table filter.
Our measure has a complete table.
So in the grand total cell, there are no filters.
This is what the bridge table looks like.
And when this filter gets passed over to the fact table,
the RRR record is not included.
Now we have one last pivot table we'd like to create.
I'm going to click in G3, use the keyboard Alt-N-V.
Because we selected an empty cell and we have something
in the data model, that's the default, click OK--
from the Books table, Book Title, from Authors,
Author in the column, from fSales, total units table
filter.
And there's our crosstab.
We get the correct totals at the bottom of each column
and the correct row totals.
Our report requires that we show both 228s for this book
for the two different authors.
But we want the correct grand total there.
All right, that was a little fun with how
to create a bridge table, how to use crossfilter
in a table filter.
And of course, over and Power BI Desktop,
we have Power Query too.
So we can create a bridge table and use bi-directional filters.
All right.
If you liked that video, be sure to click that thumbs up,
leave a comment, and subscribe because there's always
lots more videos to come from ExcelIsFun.
All right, we'll see you next MSPTDA video 29.