Creating function based on Power BI Parameter table

[Carthen]

Hello, I am trying to create a function using M that will concatinate two parameter values from a table I loaded into PowerBI to create a functional URL (report server name & Report Path), but I cannot seem to get the function to reference the local table correctly.

My PBI table looks like this:

parameter	value
RptServer	http://<ssrssvr>/ReportServer/Pages/ReportViewer.aspx?
Rpt1	%2fProduction%2fRpt1&rs:Command=Render

My function looks like this:

let RptParam=(TableName,Param) =>

let

Tbl = Source{[Item=TableName,Kind="Table"]}[Data], <-- probably not correct!

param1 = Table.SelectRows(Tbl , each ([parameter] = "RptServer")), <-- hard coded report server path

param2 = Table.SelectRows(Tbl , each ([parameter] = Param)), <-- report I want to call

value1 = Record.Field(param1{0},"value"),<-- hard coded report server path

value2 = Record.Field(param2{0},"value"),<--obtains path based on the report I want to call

urlstr = value1 & value2 <-- concatinate report server to report path to create entire URL to use

in

urlstr

in RptParam

I would then like to call the function by passing in the Parameter table name as TableName, then the report I want the URL for as the Param parameter.

the error I get is:

Expression.Error: The name 'Tbl.SelectRows' wasn't recognized. Make sure it's spelled correctly.

I cant seem to get the query to reference the paramtable table correctly.

Please help me correct this m query.

thank you in advance!

[Omid_Motamedise]

Use this one

let RptParam=(Source,Param) =>

let

param1 = Table.SelectRows(Tbl , each ([parameter] = "RptServer")), <-- hard coded report server path
param2 = Table.SelectRows(Tbl , each ([parameter] = Param)), <-- report I want to call
value1 = Record.Field(param1{0},"value"),<-- hard coded report server path
value2 = Record.Field(param2{0},"value"),<--obtains path based on the report I want to call
urlstr = value1 & value2 <-- concatinate report server to report path to create entire URL to use
in
urlstr
in RptParam

[Carthen · Accepted solution]

Thank you for responding, but I found another way around this. I still have my sql param table in the database, but I have instead created a view in sql that concatinates the server to the various report paths in a sql view and then pivots the data into a single row with however many reports listed I want to use. Once I import that view into PBI, its a simple manner of creating a measure that uses the proper column. 

FYI, I use this method to allow navagation from the PBI file to an SSRS detail report.

Thanks!

[lbendlin]

Instead of "Source"  you need to point to the name reference for the other query.