Import the First Sheet in a Workbook with Power Query

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

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.

  1. Write the query to give you a list of sheets in the workbook.
  2. 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.

Share?

Comments

  1. Great tip. Thanks ! You can probably also keep the first row in the sheets list and proceed from there.

  2. 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.

  3. 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}….

  4. 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.

Leave a Reply