Demote headers before expanding

[ACVMontreal]

Hi, I have a problem with my data. I am reading multiple files in a sharepoint folder. Initially I had csv files, I had some trouble since the name of the files were differents I opted to change for an excel format where my sheets are all named the same.

Now I have a different problem I didn't have with csv. I have to transform the data before using it,  it's not a perfect table, all the files have a title (where the date is included) . The thing is, when I expand, powerquery names the first column as the title, which contains a date, so the title of every file is never the same. 

The problem is that since it gives that name to the column, it cannot find the same column in my other files. As a result I only get the complete first column of the first file, the column is then "null" for all the other files. 

Is there a way to demote the name it gives to the column before expanding? Since afterwards it's too late, because the data is already missing. 

[ACVMontreal]

Thank you all! I didn't know you could actually have previous steps in the sample query that affected the data. So simple... Yet you have to know it's there.

[edhans]

Yes. Everything you do in the Sample Query will affect every file before they actually get combined. Can be handy sometimes. Glad to help @Jimmy801 

[Jimmy801]

Hello @ACVMontreal 

but why should power query include the name of the file in your column names? So probably you said to do so.

What you can do is to first read from the folder, read from your files at that level, in order that you get one table of every file (the columns should be alligned now, as no date is read to your column names), expand that table. So now you have your tables combined, and the name (which contains the date) of the file you can find on every row. Is this what you are looking at?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans · Accepted solution]

If you go into the "Sample Query" that Power Query generates, you can do the Demote Headers step there, or simply remove the "Promote first row to header" step it probably auto-generated.

If you need more help, can you give some screen shots to more clearly explain the issue?

[mahoneypat]

This sounds like you could use relative references vs. absolute ones.  When you have a step that specifically calls your first column by name, you get errors with the other files.  You can reference the first column for example instead of it by name.  Please see if this video helps.

https://www.youtube.com/watch?v=nscUeOL5m90

Regards,

Pat