Regarding the security, afaik the power query security is based on the user that used to create the report (same privilege as odata access. For a report that would be based on the security profile, the admin could share the excel file to the salesperson as a template and let them change the logon user that configured for the query. However, to put it as a dashboard in CRM, power query currently does not have “impersonation” or view as capabilities.

I’m completely agree that currently PowerBI for Dynamics CRM is really limited and it seems the enhancement is still on the way.

As an admin, I’m the authenticated user when I get the CRM data with PowerQuery – which means I get everything. But if I want to share this singular dashboard with salespeople, then I’d only want them to see their leads (in your example). Unfortunately, my tests have shown that this is not the case – and that the user will see data they wouldn’t normally have permissions for/access to.

I’ve been looking for an answer to this and haven’t been able to find one – and if it’s not possible, I believe that it sincerely limits the usefulness of such a powerful tool with so much potential.