Upload folder to combine one query

[cristianml]

Hi,

I would like to get only one query with a Specific Name when I upload a folder that has many Excel files but with same format.

For the moment I use UNION to combine the files but I don't want to see every single Excel files in Fields, just the combined one.

How can be done ?

Thanks and regards. 

[v-yuezhe-msft · Accepted solution]

@cristianml,

After you use Folder connector in Power BI Desktop to import data, you can right-click on the Content column and choose "Remove Other columns" in Query Editor of Power BI Desktop, then expand the Content column to combine data of all excel files in a single query.



Regards,
Lydia

[cristianml]

Thanks Lydia,

Now I can manage this. The only thing i didn't know is that Folder option only works for excel files with same tab name. 

Thanks and regards !

[MattAllington]

By default it expects the same sheet name. But you can edit the code to refer to the relative sheet. Eg Source{0}[Data] will select the first sheet. 

[MattAllington]

Read my article here. https://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/