Combine multiple Google Sheets from Google Drive

[anuradha01]

Hi 

I have a scenario in which I need to combine data from multiple Google sheets into two tables.

So each Google sheet has two tabs - Tab A and Tab B.

I need to combine all data from Tab A for all google sheets to Table A

and combine all data from Tab B for all google sheets to Table B.

I have seen some posts where it's being shown how to combine by having all files in 1 folder on your desktop.

But in my case, I was wondering if I can directly connect to the Google Drive folder where the files are and combine them all in Power BI.

Let me know if you need more information. Please note Google Sheets have same column names and structure.

Thanks

Anu

[danextian · Accepted solution]

Combine 25 google sheets by appending them to one another so they form a single table. That will be your main table. Right click that query of combined sheets and select reference. Filter name column to the desired sheet name and then do the necessary transformations. Repeat the steps, this time in another query, for the other sheet name. So you will have about 26-27 queries using following this approach (25 queries to append + 2 reference queeries for sheets A and B) vs about 50 queries (25 queries each for sheets A and B).

[danextian]

Hi @anuradha01 ,

The connector allows one to connect a spreadsheet but not to a folder similar to the sharepoint connector. Your alternative is to append each spreadsheet to the other without selecting a sheet first (Source - which is the first step in a query). That will be your main table. Reference that table and select either table A or B.  This is still quite a task for a large number of spreadsheet but removes the step of having to select a table for each spreadsheet.

[anuradha01]

Thanks for your reply - Danextian.

PREVIEW

Can you clarify what do you mean by "append each spreadsheet to the other without selecting a sheet first (Source - which is the first step in a query). "? Do you mean to say if I have 25 Google sheets manually append all Tab A of 25 google sheets and append Tab B of 25 Google Sheets and then bring them to Power BI?

[danextian · Accepted solution]

Combine 25 google sheets by appending them to one another so they form a single table. That will be your main table. Right click that query of combined sheets and select reference. Filter name column to the desired sheet name and then do the necessary transformations. Repeat the steps, this time in another query, for the other sheet name. So you will have about 26-27 queries using following this approach (25 queries to append + 2 reference queeries for sheets A and B) vs about 50 queries (25 queries each for sheets A and B).

[anuradha01]

Thanks Danextian

[anuradha01 · Accepted solution]

Hi All

I implemented using another way to combine multiple sheets with a Parameter, a Table with  URLs of all google sheets and a custom function. 

Refer 

https://youtu.be/OC5TX_xmRzk

This allowed me to change paths dynamically and make the reports scalable as well.

Hope it helps

[danextian]

That's another way to put it. Should be a google spreadsheet with spreadsheet urls in it.