Forum Discussion
Combine multiple Google Sheets from Google Drive
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
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).
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
This allowed me to change paths dynamically and make the reports scalable as well.
Hope it helps
6 Replies
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.
Thanks for your reply - Danextian.
PREVIEWCan 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?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).