Forum Discussion
Return multiple values across multiple sheets
I have multiple sheets in a workbook for teachers to record marks in tests students take. Students will progress through these tests at different rates and I want a summary of what test students are up to at any given time, when they achieved that result and what the result was (which is a percentage).
I am creating this sheet to be then imported and used in Power Bi as a dashboard for the school. Is this possible?
The last sheet titled Summary is what I would like returned from the other sheets in the workbook.
Power Query variant is in attached file
- Thank you so much Sergei, This looks like it will work for my purpose. Is it best to have the data and what is returned in different workbooks? Can you have it on a sheet in the same workbook?
It all depends on your business process. If you or someone else update results in the same file it could be done in separate sheets. If you copy/paste from another file it's more reasonable to use these other files as the source. It's also better from technology point of view.
In general that is job for Power Query, especially if you are going to publish summary on Power BI. With that it's better to keep file with subtests separately, perhaps even more better if each subtest is in separate file and all of them are kept in some folder, local one or on SharePoint.
Summary could be combined in another file by Power Query if you need such file, same query could be used to publish dashboard directly on Power BI site without intermediate Excel. You may schedule data refresh for that site with the dashboard. Thus, when done, the only job will be to update files with the subtests results.
Will try to make some mock-up bit later.
- all roads to Roman.
I guess if you provide the source workbook and expected result,the discussion on the spesific automation solution can start soon.- I have just loaded the sample and the last sheet title summary is what i want returned from the other sheets in the workbook.
This is an ETL(Extract-Transform-Load) task.
I would like running sql to automate it.
//select rowid old_rowid,colIndex[1:19] from consolidateSheet; create temp table aa as select rowid old_rowid,colIndex[1:19] from consolidateSheet where f04!='Students'; cli_one_dim_with_zero~temp.aa~4; create temp table cc as with bb as ( select *,(cast(regexp2('\d+',属性) as int)-1)/2 grp from aaunion order by old_rowid) select *,regreplace('(\d+)\/(\d+)\/(\d+)','20$3-$1-$2',group_concat(数量,'</td><td>')) Date_Result from bb group by old_rowid,grp; cli_no_header; select f03,f04,f02,Date_Result from cc;You can find other ways to acheive it too.
