how to pass parameter in Azure table Storage?

[Haritha]

 Hi Team,

 How to pass Query parameter to  URL of  Power BI Service in which the data is imported through "Azure table Storage".Please find the below URL  , Table Name,Column name

URL : https://app.powerbi.com/groups/me/reports/ReportSection?

Azure Table Name : tablename

Column name : UserID

Please help !how to pass "USERID" parameter to power bi report and get user specific report in  Power BI Service.
Thanks in Advance !

[Greg_Deckler]

You could use standard query parameters to do this potentially if it is OK that each user would have to set this parameter to their user id. Otherwise, perhaps @ImkeF knows of a way to grab the user id autmagically in M code.

[Haritha]

Thanks ! But I need how to pass the column "USERID" as a Query parameter to filter the  Power BI URL and also to get the User specific report .

For Example : Let us assume an example for "Azure Sql Server", below is the Power BI URL to pass the "USERID as the Query parameter in the URL which contains

Table Name : tablename
Column name : USERID
value : 'abc@xyz.com'

https://app.powerbi.com/groups/me/reports/ReportSection?filter=table name/USERID eq 'abc@xyz.com'

the above  URL is perfectly filtering the report with respect to "USERID", since the Power BI data is imported from "Azure SQL Server"

For Example : Let us assume an Example for "Azure table Storage" ,below is the Power BI URL to pass the "UserID" as the Query parameter in the URL which contains:

Table Name : tablename
Column name : UserID
value : xyz@abc.com

 https://app.powerbi.com/groups/me/reports/ReportSection?filter=tablename/UserID eq 'xyz@abc.com'


the above URl is not filtering the report with respect to "UserID" .Since the Power BI  data is imported from "Azure table Storage."

Please help !

 

[ImkeF]

Unfortunately, there is no native M-function to grab the user id.

But one should keep in mind, that this wouldn't work as a security, as the M-code could always be manipulated.

[Haritha]

Thanks ! But I need how to pass the column "USERID" as a Query parameter to filter the  Power BI URL and also to get the User specific report .

For Example : Let us assume an example for "Azure Sql Server", below is the Power BI URL to pass the "USERID as the Query parameter in the URL which contains

Table Name : tablename
Column name : USERID
value : 'abc@xyz.com'

https://app.powerbi.com/groups/me/reports/ReportSection?filter=table name/USERID eq 'abc@xyz.com'

the above  URL is perfectly filtering the report with respect to "USERID", since the Power BI data is imported from "Azure SQL Server"

For Example : Let us assume an Example for "Azure table Storage" ,below is the Power BI URL to pass the "UserID" as the Query parameter in the URL which contains:

Table Name : tablename
Column name : UserID
value : xyz@abc.com

 https://app.powerbi.com/groups/me/reports/ReportSection?filter=tablename/UserID eq 'xyz@abc.com'


the above URl is not filtering the report with respect to "UserID" .Since the Power BI  data is imported from "Azure table Storage."

Please help !

 

[ImkeF]

Yes, that's a different animal here.

So you have a table with different user names in one column.

And want to pass each of it as a parameter?

Are you attempting to:

1) create one table for each different user or

2)  get one big table with all (different) users data in one big table?

For 1) You can retrieve the value from a single cell from a table by navigating to it like so: TableName[ColumnName]{RowIndexNumber}

So the square brackets select the column and the curly brackets contain the row index (PQ counts zero-based)

For 2) You would have to transform your webcall to a function with the username as a parameter (or the specific url) and call that from a custom column. That would fetch all relevant tables in an additional columna which you just have to expand.

[Haritha]

But , Actually I have created a Power BI report in Power BI Desktop application using the dataset source from "Azure Table Storage" with  corresponding  Table .Then I have published my report to Power BI Service so now I got a Power BI URL . What I need is  just I need to filter the power BI URL based on "UserID" column. so  I have used the below "Query Filter condition" in the URL to filter only the specific user from among all the different users.

For Example If I am using the below URL to display only  particular user's data alone in the power BI Service report :

Table name : tablename
Columnname : UserID
Value : xyz@abc.com

https://app.powerbi.com/groups/me/reports/ReportSection?filter=tablename/UserID eq 'xyz@abc.com'

I am not getting the only the "xyz@abc.com" data alone in the result , Instead it is fetching all the different UserId's data .Please help how i need to change the above Power BI "URL query  filter conditions".

[ImkeF]

Maybe this goes into the right direction:

Web.Contents("https://app.powerbi.com/groups/me/reports/ReportSection?filter=tablename/UserID" & tablename[UserID]{0}

.. assuming the desired username sits in the first row.

[Haritha]

No, Even though I modified my Power BI Url like the URL format which you shared, it is not fetching only particular/specific user 's data in my power BI Report  , still it is fetching all the data with respect to all different users in my report. kindly refer my previous post which I said earlier     "we imported our data into Power BI  from Azure table Storage" table.

Please help me out how I can modify the below Power BI url filter conditions  to fetch the only specific user report.

Table name : tablename
Columnname : UserID
Value : xyz@abc.com

https://app.powerbi.com/groups/me/reports/ReportSection?filter=tablename/UserID eq 'xyz@abc.com' 

[v-juanli-msft]

Hi @Haritha

It seems you want different users see their own data rather than all data on a report.

I would suggest you to apply RLS for your dataset.

So that when you share a report with someone, then he logs in Power BI service with his account, he can only see his own data instead of all data.

Reference here:

Power BI – Dynamic Row Level Security – Tips to get it working!

RLS with Power BI

If you have any problem, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

I'm not familiar with the exact URL you have to use for that service, but the general principle is to incorporate values from variables like so (use " as escape signs and the & to combine the variable)

"(PartsOf)YourURLHere" & yourVariable & "OptionalOtherURLPartsHere"

You have to check for the correct URL-syntax. Could it be you have to use an equality sign ("=") instead of letters?