Encrypt parameters in Power Query

[miguelcasi]

Is there any possibility to hide (encrypt) the parameter value in Power Query? Users have to fill in two parameters (username and password) to log into the API and access the report. However, when they go to Power Query, they can see the parameters value. So, what I want is to hide the value of the password (parameter) even though they have to fill it in at the beginning. Thanks.

[mahoneypat]

I think you should use the native security features of Power BI (RLS, sharing reports/apps, etc.), but if you want to keep going with this hack approach ...

I started putting together a simple example of doing this with an Excel file; however, it isn't going to work as I originally suggested.  I forgot that, if you get the user and pass in separate queries, the credentials from the file would also be visible to others if they share the pbix.  One way around it might be to put it all in one query, but you would have to test if this prevents others from seeing it.  I made an Excel file that has two one-cell tables in it, one called Username and one called Password.  It uses a parameter called FilePath (provided by the user) to navigate to the Excel template you would provide to your users.  Here is an example:

let
    Username = 
    let
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Username_Table = Source{[Item="Username",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Username_Table,{{"Column1", type text}}),
    Column1 = #"Changed Type"{0}[Column1] //this step drills down to return the text value, not a table
    in
    Column1,
    Password = 
    let
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Username_Table = Source{[Item="Password",Kind="Table"]}[Data], //change to "Password" in duplicate copy"
    #"Changed Type" = Table.TransformColumnTypes(Username_Table,{{"Column1", type text}}),
    Column1 = #"Changed Type"{0}[Column1] //this step drills down to return the text value, not a table
    in
    Column1
in
    Username & "-" & Password //don't do this. just a demo of using both values downstream in your query

Please let me know if this works out.

Regards,

Pat

[mahoneypat]

Maybe I'm not clear on your use case.  If you are using the Power BI service, you can build reports and not let your users have access to the query, parameters, etc.  If you are trying to do with Power BI Desktop only, do your users need to be able to refresh and get all the data?  Or just a certain subset of the data they have access to?  If the latter, you could:

1. Set up parameters for their credentials, give them a pbit file, and tell them not to share any pbix they generate (as it will have their credentials).

2. Instruct them to create a file just for them on their SharePoint or local computer that holds their credentials (give them a template so it is stored consistently in the file).  Then set up a parameter to hold the path to their file secure file.  That way, if they share the pbix, it will only contain the file path and not the credentials (i.e., others would have to provide their own filepath to refresh the pbix file).

If I misunderstood, please clarify your use case.  Who can see what, etc.

Regards,

Pat

[miguelcasi]

Hello again @mahoneypat, thanks for all your patience and sorry for my insistence. Our idea would be similar to the first option you propose: share a .pbit with our clients and have them enter their username and password (parameters). So far all great. The problem is that, as you say, they can save that template as .pbix and share it with people who can see their credentials when accessing Power Query. So we would like to be able to hide the parameters in PQ. As it is not possible, I would choose the second option that you propose. However, it is still not very clear to me how to do it. I have two main doubts:
1. The file that they create with their username and password, what type of file should it be? (txt, excel, power bi, ...).
2. So, the parameter would be the path to the file, or would there be a path that lead to the parameter? I do not know if I have explained it well, but it is the one that is less clear to me.

Thanks again.

Regards,

Miguel Angel

[mahoneypat]

Here are some links to get you started.

Row Level Security

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Connecting to Excel on OneDrive & Filepath parameters

Use OneDrive for Business links in Power BI Desktop - Power BI | Microsoft Docs

Changing File Location Using A Query Parameter In Power BI | Enterprise DNA

Regards,

Pat

[miguelcasi]

Hello again @mahoneypat, thank you very much. About the second option (that the parameter contains a file path), I can't figure out how to do it as I don't see how it relates to my case. On the other hand, in the case of RLS, my idea would be to block access to all possible users, and that only I can see the parameters in Power Query. Is there an option to do that directly and not have to block access to data (parameters) for each user? Thanks.

Regards,

Miguel Angel

[mahoneypat]

One option might be to have the parameter contain a filepath to a file that contains their credentials that only they have access to (e.g., on their OneDrive).  That way, if someone else gets their pbix, they would only see the path to the file (that they don't have access to).

Of course the other option would be to leverage row level security to limit what people can see.

Regards,

Pat

[miguelcasi]

Hi mahoneypat, thanks for your response. I am relatively new to Power BI, so I would need a web page that explains how to do the two options you mention. If you have any I would appreciate it.

Regards,

Miguel Angel