Kartikey has 6800 rows of data. For each product in column A, he needs every combination of Column B with Column C. In this episode, Bill uses Power Query and a Full Outer Join to solve the problem.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2424. All combinations using power query.
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Today's questions sent in by Kartikey.
This one threw me for a loop but it's actually easy to do.
For product A, Karticky needs all of these codes combined with all of these numbers. There's not the same number, right?
So if we have five of the first and seven in the second, we want 35 rows.
We are going to do this using power query.
So the first thing I need to do is make it into a table with CTRL+T like that.
And we are going to do 2 initial queries that will be sub queries.
So here on the Data tab, this used to say, “From Table or Range”.
In my beta it now says “From Sheet”. And we call this step one.
And what we're going to do is we don't need column C in this one, so I'll remove.
And then anytime that this is null, I'll get rid of those.
Alright, So what we have is all of product a. Those are the codes from B.
And we're going to click Close and Load To….
Only create a connection. Click OK.
Alright, so there's our step one Now, we do From Sheet. And this will be called Step 2.
It's going to be exactly the same thing, but will be getting codes from column C.
So I'll remove column B. Get rid of the nulls here. Click OK.
Close and load to…. Only create a connection.
Alright, so we over here we have connections for step one and Step two.
From a blank cell, Get Data, Combine queries, Merge.
And from step one we use product.
From Step 2 use Product Select a full outer join - all rows from both. Click OK.
Expand Step 2 so we can get the item C. We don't need the original column name as prefix.
And close and load. 16,390 combinations.
This is a great time to talk about the new second edition: Master Your Data.
This is formerly M is for (Data) Monkey. It is now available.
Click that I in the top right hand corner.
If you like these tips, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank Kartikey for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Today's questions sent in by Kartikey.
This one threw me for a loop but it's actually easy to do.
For product A, Karticky needs all of these codes combined with all of these numbers. There's not the same number, right?
So if we have five of the first and seven in the second, we want 35 rows.
We are going to do this using power query.
So the first thing I need to do is make it into a table with CTRL+T like that.
And we are going to do 2 initial queries that will be sub queries.
So here on the Data tab, this used to say, “From Table or Range”.
In my beta it now says “From Sheet”. And we call this step one.
And what we're going to do is we don't need column C in this one, so I'll remove.
And then anytime that this is null, I'll get rid of those.
Alright, So what we have is all of product a. Those are the codes from B.
And we're going to click Close and Load To….
Only create a connection. Click OK.
Alright, so there's our step one Now, we do From Sheet. And this will be called Step 2.
It's going to be exactly the same thing, but will be getting codes from column C.
So I'll remove column B. Get rid of the nulls here. Click OK.
Close and load to…. Only create a connection.
Alright, so we over here we have connections for step one and Step two.
From a blank cell, Get Data, Combine queries, Merge.
And from step one we use product.
From Step 2 use Product Select a full outer join - all rows from both. Click OK.
Expand Step 2 so we can get the item C. We don't need the original column name as prefix.
And close and load. 16,390 combinations.
This is a great time to talk about the new second edition: Master Your Data.
This is formerly M is for (Data) Monkey. It is now available.
Click that I in the top right hand corner.
If you like these tips, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank Kartikey for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
