Pulling in Excel files in a folder, but the files' columns don't line up

[mmace1]

Hi, 

So say if I want to point Power BI at a folder with 10 Excel files, and those files all follow the same column order - no problem. I can grab all 10 files, grab the content from them, filter out the headers, etc. 

But say those 10 Excel files have different columns.  But, in every file waht I want, is the column named <X> - is there a way to pull in all the <X> columns from these hypothetical 10 files, and mergte all those <X> columns into 1 column? 

Thanks, 

[v-yuezhe-msft]

@mmace1,

Add a new blank query in Power BI Desktop, then copy and paste the following code to Advanced Editor of the blank query.

(filepath, sheetname)=>

let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Sheet_Data = Source{[Item=sheetname,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet_Data, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"YourcolumnName"})
in
    #"Removed Other Columns"

After that , you can enter file path and sheet name in the function to get only X column of each file, then you can use Append queries in Power BI Desktop.




Regards,
Lydia

[mmace1]

Thanks, so to be clear, for say every file added in this folder, one would need to: 

Add a new invoked function, so say - once we're up to 70 files, we'd have 70 invoked functions. 

Then, append it to the main query? 

i.e. - this wouldn't be automated at all as new files went into this folder, someone would have to take note of a new file, then add it via an invoked function/ etc.?

[v-yuezhe-msft]

@mmace1,

I couldn't think of a better method to  automate this process.

Regards,
Lydia

[Greg_Deckler]

Sure, you could use 10 different queries to pull in each file and then use an Append query to mash them all together.

[mmace1]

True!  But sorry, I only meant "10" as a hypothetical.

In reality...say more files are constantly coming in.  If each file = a new query that has to be built, it would be faster to do everything by hand, and skip Power BI at that point. 

The files don't have consistent columns.  But, I could enforce say "the column that has <X> - label it <X>" - any way for Power BI to automatically seek out that column?