Forum Discussion
Repeat Power Query for different Tables in the same workbook
Anonymous
I said leave only the tables with data. You have Planinha1 and Join as worksheet still. Why? Remove them. Then it will work. An alternative is to remove the corresponding rows for those two worksheets in the M code, immediately after the Source step in the main query. Again, do not load the result of the main query (Consulta1) to an excel table, load it through a connection only into the data model. And again, once this works I would recommend keeping the data tables in one file and the queries and processing in another one (you would only have to change the way you get your tables at the beginning of the main query, since it will not be with Excel.Currentbook( ))
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi Anonymous
Why are there 40 queries if you have 15 tables?
Sure, you should definitely use a function. If the steps to be applied to each table are the same (or very similar). You can load all the files in the folder (it's very simple, just: New Source-->Folder ) you'll then get a table that shows all files, one per row. You can then create a custom column that will apply the function to each file (extracting the table of interest and transforming it as required). Finally you can join all the resulting tables.
You can use the code you already have to transform one table to create the function. By the way the final format you would want would be something like the table below. You'd need some additional (un)pivoting:
| Date | Name | Type of metric | Result |
| 01/01/2020 | Employee1 | Metric1 | 1% |
| 01/01/2020 | Employee1 | Metric2 | 127 |
| 01/01/2020 | Employee2 | Metric1 | 3% |
| 01/01/2020 | Employee2 | Metric2 | 256 |
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers