Extend Template App Dataset with additional data

[richardkong]

So I've connected to a PowerBI template app via App Source and installed it in PowerBI service and would like to model some additional data on top of it.

I can see how I can:

1. Connect from PowerBI Desktop and access the data from that template app to create additional reports. However when I do this, I can no longer edit the dataset or add additional queries to merge the dataset
2. Create additional reports from the template app's dataset from PowerBI Service. However again there is no option to further extend the data model with additional tables
3. Create an additional dataset and create a second report off of that new dataset and create a dashboard that shows the template app data next to the new data. However this doesn't allow me to easily combine filters and I'd have to add the same filter on both pinned report tiles or live pages to be able to compare the same data.

However, I would like to take this dataset from the template app and model it with some additional data that I have on a local spreadsheet or cloud spreadsheet (e.g. google sheets). E.g. Let's say the template app dataset has a list of locations and the address for each and I'd like to link a new table from a spreadsheet that contains a list of sales by location in a many-to-one sort of relationship. Is this possible to do in PowerBI Service or desktop? It seems I can hack some of this using excel spreadsheets instead and create pivot tables off of the data but that seems more fragile than using the modelling tools in PowerBI directly. I had a brief look at dataflows too but those could not connect to the dataset from the template app.

[v-easonf-msft]

Hi , @richardkong 

Refer if these two links can help:

https://docs.microsoft.com/en-us/power-bi/service-template-apps-tips#known-limitations

https://community.powerbi.com/t5/Service/How-to-change-the-server-or-ODATA-URL-at-the-new-Template-A...

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richardkong]

Thanks @v-easonf-msft ,

As far as I can tell, the first link discusses the limitations of what can be added when creating a template app from what I gather but not necessarily what can be done with the template app dataset once you have added it to your workspace? i.e. can I take the dataset from a template app and then merge it into another dataset that I might be able to create using PowerBI Desktop?

The second link shows how to use parameters in the template app which is handy so for example I could add an optional parameter for a url to a spreadsheet that if provided is then merged into the template app dataset, however this does not allow much flexibility in the modelling and assumptions would have to be made about the contents of the spreadsheet for it to work.

My guess is that you don't allow users to use a template app's dataset to merge into another dataset to avoid cyclical references? So dataset A does not refer to dataset B which refers to A etc... However this does not help in the case where say I install your salesforce template app and then want to associate some additional data that I have from other sources to that same dataset and have it all in a PowerBI report. My only option here seems to be to have both datasets separate and display them in two separate reports side by side in a dashboard and not actually 'merged'.

The closest thing I could find is https://community.powerbi.com/t5/Power-Query/Merge-datasets-into-single-report-or-use-dataset-as-sou... which seems to suggest manually copying the queries from the pbix file which is not possible if you are getting the data from a template app (e.g. salesforce, quickbooks, etc..) as far as I know?