how Can we pass USERPRINCIPLE or USERNAME as a query parameter in Power Query

[Anonymous]

Dear experts:

Now I'm facing a difficulty on data protection: there is a tableA in sql server database, our team has 3 powerbi desktop developers, each developer should only get restriced rows in tableA, for example developerA should only get those rows whose [CompanyID]=A. Is that possible to pass USERPRINCIPLE or USERNAME as a query parameter in Power Query? 

TableA:

CompanyID	Column1	Column2
A	XXX	XXX
B	XXX	XXX
C	XXX	XXX
…	…	…

[Anonymous]

Oh I forgot about the solution I came up with years ago for this. if you have a Windows machine, chances are you can use Folder.Contents, and use C:/users as the folder path. Sort by Last Modified descending--usually, the user is going to have the most recent last modified date, because he's the current user. Grab the username from the user's folder. Set that query to refresh upon opening.

You just can't beat Power Query for stuff!

--Nate

[Anonymous]

It is possible--I showed you how in my solution.

--Nate

[Anonymous]

I've set something up like this. First, you have to have A data model set up in order to use the userprincipalname function. So I just made a table with today's date that loaded to the date model. Then I added the userprincipalname function to the data model, and loaded the result to a page in Excel. I then used that cell with the user principal name as the source of a new query, which you can then use as a filter/parameter/whatever you like. It's important that you set that query to load upon opening the file, so that the current user's user principal name is ready when you refresh the rest of the data.

--Nate

[asadmd93]

@Anonymous , I agree to @Gabry . There is data masking but might not server your purpose - https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-securit...

[asadmd93]

Here is how can you achieve it.

1. Create a Security Table: Create a table in your database that maps users to their respective CompanyID through the email. 

2. Import the table into Power BI

3. Create a relationship between the security table and the other table based on the CompanyId. 
4. Implement Row-Level Security:

• Go to the Modeling tab in Power BI Desktop.
• Click on Manage Roles.
• Create a new role called RLS and then select DAX editor and set a DAX filter on the UserSecurity table to filter rows based on the logged-in user. Use the USERPRINCIPALNAME() function to get the emailid of the person. 

5. Use the View as in the modelling tab and select the role and add the emailid to test the RLS.

That's how the data is viewed before RLS is implemented

That's how the data is viewed after RLS is implemented

6. Once it is done, you can publish it on the workspace. Go to the semantic model and then Select Security and the respective roles and add the emailid of the respective person in the security table to implement the RLS on power bi service. 

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

[Anonymous]

Dear asadmd93:

Thanks for your reply. I know row level security is a good solution for report viewers. But what I refered is powerbi desktop developers, means that these developers has build access on the published semantic model, so when they connect to this semantic model they can get all data in this semantic model. That's why I ask for a solution via power query.

[Gabry]

Hello @Anonymous ,

those functions are DAX functions and not M. You can't use them in power query. So short answer is no (as far as I know).

But why would you like to send this parameter to the source? Couldn't you load all the data into the semantic model and then apply RLS as usual?

Is this a direct query model?

Oh sorry you are speaking about PBI developers, noticed now.
Then just make some views on the source and give access only to the specific view, or apply some security stuff directly to the SQL source. You have to manage this on the sql server side

BTW, it doesn't make sense to pass developer username because as a developer you can fake it as you want

[Anonymous]

HI, Gabry:

thanks for your reply. Create views in source database is a good solution, but the semantic model has 7-8 tables, and developers can be divided into 5 access group, so we have to create too many views.

We realy hope that power bi can pay attention to row level control for power bi desktop developers, so that developers(builder role) can have a way to get same access limitation as viewer role.

[Gabry · Accepted solution]

I understand your needs but I think it's not possible. I can't figure out other solutions.

The only thing I can still suggest is to duplicate the semantic model, or make a specific dataflow for each developer, then each developer will connect to his own dataflow