Power Query from Folder

[Rcaaa]

Hello,

I set up a power query from folder. When I put in another folder I receive an error that says OLE DB or ODBC error: Expression Error. The key did not match any rows in the table.. An error occurred while processing table 'LDP5'. The current operation was cancelled because another operation in the transaction failed.


Does anyone know what I am doing wrong?

Thanks,

RCAAA

 

[Craigc3814]

How many files are in the folder you are pulling from? If you have spreadsheets ANYTHING that is not the same as the first file you used when creating the connection this is what is causing your error. Column Headings and the Sheet Name have to be IDENTICAL unless otherwise specified within the PQ code. Same goes for any file where PQ pulls in the headers for you, if the headers change file to file it will not work.

If it is just the folder causing an issue you can remove it from the current folder OR you can change the PQ code to filter out different file types. For example I pull from a folder that has .txt files, spreadsheets, other fodlers, PDF's, etc. The first step of my code after pulling in that folder is to filter out any file type that is not .txt

 

[Rcaaa]

The error message got shorter, thanks! It now says the key did not match any rows in the table. (An error occurred in the ‘Transform File from Query1’ query. Expression.Error: The key didn't match any rows in the table.)

All of the columns are the same. The (2) files are .xlsx. The sheet names are now identical. Any other guesses on what is going wrong?

 

[Craigc3814]

Sounds like something is still off with the column names or sheetname. Go into editor and work backwards through the steps and see where the problem starts. If it's the very first step it's a naming issue. Even if there is a space after a column heading that isn't visible it causes an issue. I only know from experience. The other thought could be you have a row in your data that doesn't like what you are trying to change the data type to. An example would be a text row in a column you're trying to format as a number

 

[Rcaaa]

Craig,

Thank you for your help.
I was able to fix it by changing the table names. I had to select the new table name in my PQ and it worked.


Thanks,

RCAAA

 

[Craigc3814]

No worries, like I said I only know because I experienced and someone here helped me figure it out.

Keep pursuing the PQ knowledge it will pay large dividends in your career. I cannot recommend Ken Puls book M is For Data Monkey and Rob Collie's PowerPivot book enough.