My series about common patterns for loading data using Power Query received quite a lot of interest over the last few weeks. You can review those articles in the links below
- Combine all files in a folder with Power Query.
- Load the Latest Version of a File with Power Query.
- Combine all sheets in a workbook
The other day I was helping a client and he asked me about another scenario; import the first worksheet from a workbook, what ever it is. I thought this would be a good extension to the above articles.
Actually, the process I show you here can be used to import the first “anything” from a list. It doesn’t matter if it is the first sheet in a workbook or the first file in a folder, or what ever, the priisolution is still the same.
Import Only the First Sheet in a Workbook with Power Query
There are a couple of tricky issues to overcome with this scenario.
- Write the query to give you a list of sheets in the workbook.
- Make the query work regardless of the name of the first sheet, so it loads the first one in the workbook regardless of its name.
I cover these tricks in my video below.
Want to Learn Power Query in a Structured Way?
My Power Query Online training course consists of 7.5 hours of online video training material that will teach you everything you need to know about using this fabulous tool for both Power BI and Excel. You can watch some free sample videos and find out more about registration following the link above.
Why always some long, over explaining video when you just need to post a quick couple sentence paragraph and a single screenshot. So many people do this, doesn’t seem to matter what you’re looking for. Few people can get to the point. Nice extra explanation, but for someone just looking for the quick answer, the videos are a real PITA.
“Item=Source{0}[Item]” < that's the answer
I’m sorry my free help inconvenienced you so much.
Thanks! Clear and concise explanation!
Brilliant, Thanks so much for this
Thanks Matt great tip. And very good video . Just as good as GuyInACube 🙂
That’s the nicest thing someone has said about my work for some time ?
Hi Matt,
Great video on “Import the First Sheet in a Workbook with Power Query”. I generally prefer written articles I can skim through as so many videos seem to be excruciatingly noddy and slow, too high level, but this was spot on. One specific user case/problem, relevant, well explained solution.
Thanks!
Grant
Thanks Grant. There are some people that produce pure video content. My preference is still for a blog (step by step instructions), but sometimes the effort of writing those instructions is so high, that I just record a video instead. And I agree, some videos are so slow – it is as if some people are trying to pad it out to create a longer video. I say “just give it to me” 🙂
Your video was most helpful. Thanks!
Great tip!
Hi, Thanks for the tip.
Can you help me with the same issue when selecting “files from folder” in which the file contains data from an ERp system and the sheet name changes. Because of the changing sheet name the query is not succesfull.
You need to turn on the formula bar so you can see the code generated. There will probably be a navigation step something like this…
= Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data]
This code is “hard coded” to a “sheet” named “Sheet1”. You need to change the code so it simply picks the first Sheet. Assuming you have no tables etc, you can just edit the above line to this….
= Source{0}[Data]
This will pick the first item in the list, and as long as you only have sheets, it will work fine. If you have more than just Sheets, you will need to filter the list of items to keep just sheets first.
I too was facing almost similar problem, and just came across your reply and the below piece of code came as a rescue to me. Thanks!!
= Source{0}[Data]
Thank you!!!
Hi Matt,
I had several workbooks that contained more than just worksheets. In all the workbooks I examined, it seemed like the worksheets were always listed first, but I wasn’t sure I could depend on that. To ensure that I extracted worksheets only, I found the Table.SelectRows function was useful for selecting only the rows of the workbook where Kind=”Sheet”. Then the data from either the first, Nth, or last worksheet could be extracted.
For example:
// First worksheet
Table.SelectRows(Source, each ([Kind]=”Sheet”)){0}[Data]
// Second worksheet
Table.SelectRows(Source, each ([Kind]=”Sheet”)){1}[Data]
// Last worksheet
Table.Last(Table.SelectRows(Source, each ([Kind]=”Sheet”)))[Data]
// Second-from-last worksheet
Table.LastN(Table.SelectRows(Source, each ([Kind]=”Sheet”)), 2){0}[Data]
Great tip Matt
I would have used an Index Column
There’s a few ways. probably easiest is to select “Keep last rows, 1” and then import the one that is left. If you want the second to last, then keep last rows, 2, and take the top one.
Hi Matt, thanks for the suggestions. How would you import the last sheet as opposed to the first sheet? For example where you changed the M code reference for June to {0} in order to always return the first sheet regardless of the sheet ‘s name how would we change the {0} to pick up thus the last sheet. Thus using {Last} would not work… (That’s what I would like to do!) And if I wanted the second last sheet thus it would be {Last – 1}….
Hello,
Thanks for sharing. A further question on this – how would one always pick up the last sheet and or the second last sheet. Especially if you are doing a merge / compare of the two most recent months or years of data etc. Not uncommon for the latest data to be added at the end in XL rather than the start… Thanks!
You could use the standard table row functions in Power Query. There is a “Keep top Rows” that you could use to keep just the first row (instead of what I did). There is also a “keep bottom Rows” that you can use to keep just the last one (one of your questions). There is also a “remove bottom rows” which you could use to remove the last page, then “keep bottom rows” which would keep the new bottom row (previously second to last) – your second question. Hope that makes sense.
Great tip. Thanks ! You can probably also keep the first row in the sheets list and proceed from there.
Oh. Good point 🙂