Union

[Anonymous]

Hi, 

I need to union 4 different excel spreadsheets together which are in different folder locations. I know I can import them separately and then use the append feature, but at best it seems this would copy three of the spreadsheets and then append them to the fourth (hence leaving three spreadsheets still in Power BI and making the it a very large file). All four spreadsheets are very large so Ideally I'd just like one single query which unions all four spreadsheets together. Looks like I'd need to move all four spreadsheets to the same folder and then import together, but I wondered if there was another way? Mnay thanks for any help, and apologies if i have not explained things very well. 

[Icey]

Hi @Anonymous ,

One workaround, please check:

1. Connect to the folder containing all required files and then choose "Tranform Data".

2. Transpose the whole table.

3. Choose the columns you need and then remove other columns.

4. Transpose the table again.

5. Combine files.

Then, you'll get what you want.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @Anonymous ,

In the query editor you can reference differents steps in you query, and do not need to follow the order in wich they are placed, this meaning that within a query you can go reference previous steps. How this can help you is that you can create the 4 source files and the make a merge with the previous steps.

Check the code below with an example:

let
    Source = Excel.Workbook(File.Contents("C:\File1.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    Source_2 = Excel.Workbook(File.Contents("C:\File2.xlsx"), null, true),
    Table2_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    Source_3 = Excel.Workbook(File.Contents("C:\File3.xlsx"), null, true),
    Table3_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    Source_4 = Excel.Workbook(File.Contents("C:\File4.xlsx"), null, true),
    Table4_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Appended Query" = Table.Combine({Table1_Table, Table2_Table, Table3_Table, Table4_Table})


in
    #"Appended Query"

This is a very basic example were all 4 files are exactly the same and stored in the same place the trick here is on the last step the Appended query where you must refer to the last step of the transformation for each file in my case it's the Table1_Table and so on, but for example if for file 3 you added additional steps to transform your query you needed to refer to that last step.

You now have a single query loading all the files.

Hope this helps.