Automate making many report pages from template, parameters and folder dataset import!?

[Anonymous]

Dear community, 

I am a new PBI user in the field of building energy. Have been trying something new and need help with some directions on best approach to create a data model! Any tips or pointers you can provide me would be very much appreciated: @OwenAuger , @Anonymous 

So, I have a template style report page which is essentially different bits of data from a building energy simulation (first image below). I now want to create many versions of this report page in the same pbix file from a series of similar parametric simulation results (standardized output format), i.e. I have the exact same output file in .csv hooked to these graphs and have a series of parametric runs (second image) stored in a folder that I need to generate these pages for each one. The issue is there are many of these parametric runs and I need an automated way of doing this. So far I have looked at the options below:

1. Saving the report page as template file and hooking to datasets (results of parametric runs)
1. Issue is I would need to (I think) manually connect each one of these parametric results to the template and generate a separate pbix file which is a problem and not feasible automatically the best I know now…I need these many report pages of parametric runs in the same pbix report so that I can do additional analysis on them…
2. Combine excel files from a folder: was originally thinking there might be an easy way to direct PBI to look at the folder that has subfolder of all the parametric runs and somehow have it automatically “batch import” + connect to a template?
1. Haven’t made many strides with this option but so far have found out that I can extract the folder paths somewhat easily with the “folder” source file option (third image below). The folder path can be defined as a parameter to be used with a template file I guess but not sure how I can automate the process of opening many files and creating one pbix file instead of what would come out of a template file approach which is many pbix files!
2. The other problem I am having with this is how to specify in the import process the file format (.csv) that I need PBI to import not the rest of formats that are generated as a result of the simulations. I am thinking there may be a way to specify the file properties, including the path and format in an interim import query?
3. Not sure if this matters much but the file path with all the numbers is a job ID number from the optimization software outputs that is a way of mapping the combination of unique input parameters. That job ID key isn’t directly reported in any of the output files in these scenario folders but I may be able to somehow create that “tag” in the output files to leave a marker for defining relationships. I am only mentioning this because this optimization program actually spits out a combined results excel sheet (last image) that has these job ID tags. The issue is each row in that file only has a singular value of input or output (think about it as a series of singular KPIs for that row) but the files in each subfolder have hourly time series that I need to access for these visualization. Essentially I have two levels of data scales associated with each scenario: 1) singular value, KPI numbers stored in that combined excel sheet 2) hourly time series files that are stored in the folder. I need to create relationships that connect the two dimensions of data to each simulated scenario and visualze them in two scales...

[v-alq-msft]

Hi, @Anonymous 

Based on your description, It is unsupported to create many versions of a report page in the same pbix file from a series of similar parametric simulation results.

As part of importing a template, users will be asked to provide values for parameters defined in the template. Once Parameter values have been specified, a new PBIX file will be created, containing all Report pages, visuals, data model artifacts and queries as the original PBIX file, but containing the data based on the current user’s credentials and parameter selection. So one version of report page corresponds to one pbix file.

For further information, you can refer to the following links.

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/

https://docs.microsoft.com/en-us/power-bi/desktop-templates

Best Regards

Allan

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