Reply
Regular Visitor
Posts: 26
Registered: ‎07-22-2015

Merge datasets into single report or use dataset as source

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.

 

thnx

 

Warren

Moderator
Posts: 4,334
Registered: ‎03-10-2016

Re: Merge datasets into single report or use dataset as source

Hi @warrencowan,

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.

Thanks,
Lydia Zhang

Regular Visitor
Posts: 26
Registered: ‎07-22-2015

Re: Merge datasets into single report or use dataset as source

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?

 

Moderator
Posts: 4,334
Registered: ‎03-10-2016

Re: Merge datasets into single report or use dataset as source

Hi @warrencowan,

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.

Thanks,
Lydia Zhang

Regular Visitor
Posts: 26
Registered: ‎07-22-2015

Re: Merge datasets into single report or use dataset as source

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

Regular Visitor
Posts: 26
Registered: ‎07-22-2015

Re: Merge datasets into single report or use dataset as source

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.

Regular Visitor
Posts: 26
Registered: ‎07-22-2015

Re: Merge datasets into single report or use dataset as source

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

Regular Visitor
Posts: 26
Registered: ‎07-22-2015

Re: Merge datasets into single report or use dataset as source

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.

Highlighted
Moderator
Posts: 4,334
Registered: ‎03-10-2016

Re: Merge datasets into single report or use dataset as source

Hi @warrencowan,

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.
1.PNG2.PNG

Thanks,
Lydia Zhang