The MSFT Sweeps URL: Microsoft Forms
Thanks to Matt Allington for this technique. Check out Matt's book: Supercharge Power BI with DAX book
Today's video: How to combine data from multiple worksheets using Power Query. Combing multiple workbooks is easy, but combining Sheets is now possible, even though the method is mostly hidden. Thanks to Matt Allington, see how to solve this common problem.
Thanks to Matt Allington for this technique. Check out Matt's book: Supercharge Power BI with DAX book
Today's video: How to combine data from multiple worksheets using Power Query. Combing multiple workbooks is easy, but combining Sheets is now possible, even though the method is mostly hidden. Thanks to Matt Allington, see how to solve this common problem.
Transcript of the video:
OK Alright Alright.
Thanks for joining me on the behind the scenes.
I'm here in this beautiful podcast booth on the show floor at Microsoft Ignite. There's a theater right behind me.
They're talking about something. Who knows what they're talking about.
Learn Excel from MrExcel Podcast Episode 2295 Power Query, Combine All Worksheets! Not all files.
All files: that's awesome, we've done that on the podcast before.
This one is exciting. I want to thank you for joining us again.
I'm Bill Jelen from MrExcel.
This is being recorded at Ignite 2019. I'm here in Orlando.
They gave me a beautiful podcast booth. They gave me an hour to record 1 podcast.
I said "one podcast?!" I can do more than that so I'm going to see how many we get through today.
Today: our question is how can we combine all data from all worksheets in one workbook.
Not combine sheets from multiple workbooks? That's easy.
Now the thing to be aware of here, this came from my friend Matt Allington, author of this awesome book, Supercharge Power BI.
You want to check that book out?
This is not as quote unquote, "Smart" as Combine Workbooks.
You need to make sure all sheets have the same columns in the same order for this to work, so we're going to do is we're going to say.
Data, Get Data From a File, From a Workbook. PowerQueryCombineSheets.
There we are.
So we choose power query combined sheets all right now.
Here's the real confusing part of this, right in the first step.
Here, they're going to show us a list of all of the sheets, and this is the thing that you'll never figure out and let's you meet Matt (Allington).
Or you see this podcast or someone else who knows this trick.
We're going to right-click here and not load, not load to.
We're going to say, Transform data, Transform data.
This is like some secret mojo magic Power Query.
Alright, and what we get this is the sheet name.
You see that we have sheets and defined names.
I'm going to use the filter to get rid of the defined names. That gives us just the sheets.
Oh, and by the way, these are sectors, communications, energy, financialm health care, manufacturing, retail, transportation, not this. Guess what? We don't want that sheet.
That's not this.
Every workbook has that sheet like the one with instructions or boilerplate or something like that.
So I'm going to use the filter here. Filter.
I'm going to say text filters, does not contain.
The word "not right" and that should leave me with just 7 rows. Beautiful. So there's the name of the file.
And then there's our data. These three I don't need.
I can get rid of these, so right click an remove those columns.
All right now the Pure Magic, There are two steps of pure magic here.
This is the second one.
Expand this, we want to select all columns and uncheck use original column name as prefix.
Choose OK and now it just loaded every single row from every single sheet in that book except for the sheet called, "Not this". All right now.
A couple of steps here to clean this up, we're going to say use first row as headers that moves the first row up into the headers.
There's headers on every single sheet.
So anytime we see the word Customer here, down below, we need to get rid of that. Because that is the header row from all those sheets.
Click OK. And look at that. Do we still need this?
This was actually called worksheet name or which sheet.
Your call whether or not you keep that. Region, product, date.
It says we have 670 rows. Looks really really good.
Alright so Home, Close and Load. Yes. Bam there it is.
Super set of all of the data on all of the sheets. Just an awesome awesome trick.
Thanks to Matt for that.
Alright, well hey, check out that book from Matt Allington.
Supercharge Power BI has all kinds of great power BI tips and tricks, including the entire DAX formula language.
If you like what you see here, please subscribe down below and ring that Bell.
The bell will get you notifications, let you know when new episodes come out.
I want to thank Microsoft and the Ignite team for inviting me to record a podcast here.
They were having a great giveaway.
They say thanks for listening to our podcast we recorded at Microsoft ignite in Microsoft is giving away a Microsoft Surface earbuds to our listeners to answer.
Visit AKA Dot Ms Slash PodcastSweepstakes before December 15th, 2019.
Well, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Thanks for joining me on the behind the scenes.
I'm here in this beautiful podcast booth on the show floor at Microsoft Ignite. There's a theater right behind me.
They're talking about something. Who knows what they're talking about.
Learn Excel from MrExcel Podcast Episode 2295 Power Query, Combine All Worksheets! Not all files.
All files: that's awesome, we've done that on the podcast before.
This one is exciting. I want to thank you for joining us again.
I'm Bill Jelen from MrExcel.
This is being recorded at Ignite 2019. I'm here in Orlando.
They gave me a beautiful podcast booth. They gave me an hour to record 1 podcast.
I said "one podcast?!" I can do more than that so I'm going to see how many we get through today.
Today: our question is how can we combine all data from all worksheets in one workbook.
Not combine sheets from multiple workbooks? That's easy.
Now the thing to be aware of here, this came from my friend Matt Allington, author of this awesome book, Supercharge Power BI.
You want to check that book out?
This is not as quote unquote, "Smart" as Combine Workbooks.
You need to make sure all sheets have the same columns in the same order for this to work, so we're going to do is we're going to say.
Data, Get Data From a File, From a Workbook. PowerQueryCombineSheets.
There we are.
So we choose power query combined sheets all right now.
Here's the real confusing part of this, right in the first step.
Here, they're going to show us a list of all of the sheets, and this is the thing that you'll never figure out and let's you meet Matt (Allington).
Or you see this podcast or someone else who knows this trick.
We're going to right-click here and not load, not load to.
We're going to say, Transform data, Transform data.
This is like some secret mojo magic Power Query.
Alright, and what we get this is the sheet name.
You see that we have sheets and defined names.
I'm going to use the filter to get rid of the defined names. That gives us just the sheets.
Oh, and by the way, these are sectors, communications, energy, financialm health care, manufacturing, retail, transportation, not this. Guess what? We don't want that sheet.
That's not this.
Every workbook has that sheet like the one with instructions or boilerplate or something like that.
So I'm going to use the filter here. Filter.
I'm going to say text filters, does not contain.
The word "not right" and that should leave me with just 7 rows. Beautiful. So there's the name of the file.
And then there's our data. These three I don't need.
I can get rid of these, so right click an remove those columns.
All right now the Pure Magic, There are two steps of pure magic here.
This is the second one.
Expand this, we want to select all columns and uncheck use original column name as prefix.
Choose OK and now it just loaded every single row from every single sheet in that book except for the sheet called, "Not this". All right now.
A couple of steps here to clean this up, we're going to say use first row as headers that moves the first row up into the headers.
There's headers on every single sheet.
So anytime we see the word Customer here, down below, we need to get rid of that. Because that is the header row from all those sheets.
Click OK. And look at that. Do we still need this?
This was actually called worksheet name or which sheet.
Your call whether or not you keep that. Region, product, date.
It says we have 670 rows. Looks really really good.
Alright so Home, Close and Load. Yes. Bam there it is.
Super set of all of the data on all of the sheets. Just an awesome awesome trick.
Thanks to Matt for that.
Alright, well hey, check out that book from Matt Allington.
Supercharge Power BI has all kinds of great power BI tips and tricks, including the entire DAX formula language.
If you like what you see here, please subscribe down below and ring that Bell.
The bell will get you notifications, let you know when new episodes come out.
I want to thank Microsoft and the Ignite team for inviting me to record a podcast here.
They were having a great giveaway.
They say thanks for listening to our podcast we recorded at Microsoft ignite in Microsoft is giving away a Microsoft Surface earbuds to our listeners to answer.
Visit AKA Dot Ms Slash PodcastSweepstakes before December 15th, 2019.
Well, I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
