04-13-2017 09:48 AM
I'm querying Salesforce for a very specific email activity report, which only reports the details of the email activity of the user who is running the report at the time. It's daft, but nooone can get around it, and the underlying object is not visible in the API either so it has to be a query on a report.
The only way to get a consolidated view of all email activity for all my users, is to log in with each of their credentials and query the report, and then merge them all together.
I had the bright idea of doing this in PBI, but you can only query Salesforce using one set of credentials within the PBIX file or the pbi service.
I thought maybe I could create a different pbix file for each, but then I'd have multiple datasets and I can't run a report off of multiple seperate datasets or merge them. A dashabord doesnt help me either because I need a single table.
Is there anyway to use each of the datasets as a source, so that I can query and merge them all in another pbix file.
Any help much aprpeciated as always.
04-14-2017 01:31 AM
I don’t think you can use an automatic method to merge datasets into a single report. You would need to copy query of each PBIX file to a single central PBIX file, then create reports using queries in the central PBIX file.
04-14-2017 03:30 AM
Thanks for replying Lydia. The queries themselves dont actually contain the credentials used to access salesforce though. I thought they are stored in the global permissions or the permissions for the pbix file.
Or are you saying there is a query for the overal pbix file. If so, do you think you could elaborate on where to find that?
04-17-2017 03:28 AM
You can copy entire table in Query Editor of each PBIX file with different credentials and paste the data of each table to Excel file, then connect to the Excel file from a new PBIX file.
04-18-2017 04:52 PM
do you mean paste as in a static one time copy paste, or are you saying that excel can query the individual pbix files as data sources.
a one time export isnt going to help unfortunately as this a comstanty updating report.
if you mean the latter i must confess i am unfamiliar with excels ability to query powerbi
04-18-2017 04:56 PM
ok lydia scrub that last comment. ive just read up on excel querying powerbi. completely missed that release. i will try that out and report back.
04-20-2017 04:01 AM
Hi Lydia, I dont think this can work as when you change the sign in settings for salesforce in the pbix file, all the other pbix files inherit it. am I missing something
04-20-2017 04:01 AM
Hi Lydia, I dont think this can work as when you change the sign in settings for salesforce in the pbix file, all the other pbix files inherit it. am I missing something.
04-20-2017 07:15 PM
I mean that you copy entire table of each PBIX file, then press Ctrl+V to paste these data in Excel. After you merge data of these tables in a single table in Excel, then connect to the Excel file in Power BI Desktop.
05-22-2018 08:32 PM
I have the same issue. Is there a simple way to merge two or more datasets, without having to copy and paste queries for each table separately?
I am creating a core data model and the different domains are created in separate files (PBIX). There are 7 domains, each having anywhere from 13 to 22 tables. Copy and pasting each is a lot of extra work and not to mention the refresh that happens on each commit. This is why they were kept separate to minimize the load from other domains in the modeling phase.