Excel Power Query Expression Error

[MartinvanK2023]

I have the following issue. I created a query based on Excel file Invoice with a specific name. But when I replace in the source folder a different file with a different name the query gives an Expression error. I understand what the issue is but I would like to know how I can solve it? I have example files ready and an explanation in Word but I do not see how I can attach them yet.

With kind regards,

Martin

[eduardosa]

Hi Martin,

Question, can you paste the error on the message?

There are a couple of suggestions you can try, depending on the scenario:

• change the file location to a parameter,
• choose data source to folder.

But please explain better what you're trying to achieve.

Regards,

Eduardo

[MartinvanK2023]

Hello Eduardo,

I have a file available with all the print screens of the steps I took but at the end this error message appears. 

Expression.Error: The column 'Example Invoice 1.xlsx' of the table wasn't found.
Details:
Example Invoice 1.xlsx

The file is located in a folder (the data source is a folder) but when I remove the file with Example Invoice 1  and replace it with Example Invoice 2 this error occurs. The query is looking for Example Invoice 1 but it cannot locate it anymore.

[eduardosa]

If you're using folder as a data source, have you pressed binary instead of expanding the content?

This will enable you to combine the content of every/any excel file in the folder.

If you pressed binary, it will generate an error, because this is hard coded.

[MartinvanK2023]

Hello Eduardosa

This how currently the source looks, the source is a folder. The document name in the folder is Example Invoice 1

Here I choose Commercial Invoice (tab) and open the table.

I filter out the rows I do not need. It now shows the rows I need.

And here I promote the first line to headers.

But when I replace in the folder the Example Invoice 1 file with Example Invoice 2 the query gives an error as it still looking for file Example Invoice 1 despite it is in a folder. I also checked the message of Solution Sage I do not understand that one.

[eduardosa]

Hi @MartinvanK2023 ,

Replace your steps with combine files (this option is not that obvious).

This will create the transformation steps which will apply to any file you drop on the folder.

[MartinvanK2023]

Hi Eduardosa,

I initially did that and I got the error message I posted here.

This creates the source name to be the Example Invoice 1 file.

The following steps are the same. Filter out the lines you do not need. Promote the first line as header. Then close and load.

But when I then replace the Example Invoice 1 by the Example Invoice 2 in the folder. I get the error message when I refresh the data in the table.

As the query is still looking fot Example Invoice 1.

[eduardosa]

Following the step Promoted Headers, rename first column to "File Name".

[MartinvanK2023]

Hello Eduardosa,

Thank you for responding. The problem still remains. The column is not dynamic. Is should not look at this specific file but to all data that is in the folder....

[ppm1]

You query likely has a step that directly references that file name. In the query editor, find the earliest step that gives the error and look in the formula bar. You can update the hard-coded file name there, or you can learn how to do a relative reference (e.g., {0} to get the first row/record of the table).

Pat