Power Query to Retrieve Data from a Specific Sheet in an External Workbook

[Trebor76]

Hi there,

I have two Excel named ranges each of which are only a single cell:

SrcFile (this contains the full path of the workbook that has the data I want to import), and
SrcSheet (this contains the sheet name from within SrcFile workbook I want to import)

I want to import the data from SrcSheet using Power Query and have come up with the following (I'm still learning so it may not be the most efficient way):

let
    strFullPath = Excel.CurrentWorkbook(){[Name="SrcFile]}[Content]{0}[Column1],
    strSheet = Excel.CurrentWorkbook(){[Name="SrcSheet]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(strFullPath)),
        strSheetToQuery = Source{[Item=strSheet, Kind="Sheet"]}[Data]
in
    Source

But it just lists the sheets in the external workbook. What I am missing to return the actual data?

Any advice would be greatly appreciated.

Many thanks,

Robert

 

[smozgur]

The last line is currently Source in your code. It is supposed to be strSheetToQuery.
There are also missing quotes around the Name string values.
The corrected code is:

let
  strFullPath = Excel.CurrentWorkbook(){[Name="SrcFile"]}[Content]{0}[Column1],
  strSheet = Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1],
  Source = Excel.Workbook(File.Contents(strFullPath)),
  strSheetToQuery = Source{[Item=strSheet, Kind="Sheet"]}[Data]
in
  strSheetToQuery

Note: If you use the code wrappers to post your codes on the board, it would be easier to read and also spot syntax errors like the quotes above. Just select the code you paste in the editor, and click on the M icon on the editor (M for M code, VBA for VBA code, etc). I edited your post to use code tags, so you can see the difference.
Code tags:

 

[Trebor76]

Hi smozgur,

Thanks for your reply.

I'm now getting an "Expression.Error: The key didn't match any rows in the table" error message. The workbook path and sheet name are both correct so not sure what's causing the error message. The tab holding the data in the external workbook is numeric if that makes any difference.

Any idea what's causing the error message?

Thanks,

Robert

 

[smozgur]

Look at my sample below. I have "Sheet1" in the cell named "SrcSheet", which is the strSheet identifier in the M code, and I have a worksheet named as Sheet1 in the source workbook. Therefore, I got the desired result in the strSheetToQuery step.

Now, please click on the Source step in the Applied Step section on your computer and look at the table closely. Item is the sheet name in the source workbook (Kind should be equal to Sheet). The following line is basically extracting the Data table value from the Source where the Kind field is equal to "Sheet" and the Item field is equal to the worksheet name.

strSheetToQuery = Source{[Item=strSheet, Kind="Sheet"]}[Data]

Click on the strSheet step, it is your worksheet name coming from the named range. Verify there is a worksheet with this name in the source workbook and change the desired worksheet name accordingly.

 

[Trebor76]

Thank you. I had stepped through each of the applied steps and saw that the values being returned were correct. The issue was what I had alluded to...

The tab holding the data in the external workbook is numeric if that makes any difference.

...as the sheet name had to be entered as text i.e. starting with a single apostrophe for it to work.

Many thanks,

Robert

 

[Trebor76]

Actually this is more elegant:

strSheet = Number.ToText(Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1])

 

[smozgur]

Thank you. I had stepped through each of the applied steps and saw that the values being returned were correct. The issue was what I had alluded to...

...as the sheet name had to be entered as text i.e. starting with a single apostrophe for it to work.

Many thanks,

Robert

Yes, I overlooked that important note. Glad you got it.

Although it might not affect your project as long as you have the sheet name as numeric, one thing to note is that the Number.ToText() function requires a numeric value. It works fine as long as the SrcSheet value is numeric. However, it will fail if the sheet name contains non-numeric characters. In such cases, using the Text.From() function would be safer because it attempts to convert "any" type of value.

strSheet = Text.From(Excel.CurrentWorkbook(){[Name="SrcSheet"]}[Content]{0}[Column1])

 

[Trebor76]

Although it might not affect your project as long as you have the sheet name as numeric, one thing to note is that the Number.ToText() function requires a numeric value. It works fine as long as the SrcSheet value is numeric. However, it will fail if the sheet name contains non-numeric characters. In such cases, using the Text.From() function would be safer because it attempts to convert "any" type of value.

Great advice smozgur - much appreciated