Pulling multiple files from sharepoint into one query

[Anonymous]

Hi there,

I have a sharepoint folder where files are deposited holding employee data. Half the data is from our US HR system, the other half is from our UK HR system. Ergo, the column names differ.

When I pull all the files into one query, Power BI does the magical 'Transform file' thing where it uses the first file as an example - so it's ignoring any columns in files which don't have the same column names.

Assuming I can't make Human Resources rename the fields in their system, I am stuck with different column names. I reeeeaaally don't want to pull the files in separately, rename them, and append. Because there will be new files each week to append and I want an automated process.

Is there any way around this? Can I ask Power BI to bring all the columns into one query, then I can do the data modelling in my applied steps? 

Jemma

[mh2587]

Get all the data from the source in different quries later merge it

[PaulDBrown · Accepted solution]

Why not store the files in separate folders (US & UK) and then import them using the folder option for each, adjust the "rogue" column names & transformations and finally append the separate queries into the final one?

[Anonymous]

Yeah I guess that was a last resort. I might have to by the lack of replies to this thread! Thanks @PaulDBrown 🙂