Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Parameter to switch between source queries

Hi,

 

I have two source queries containing the exact same data.

 

I have 10+ reference queries, currently referencing one of the above.

 

I created a parameter that can switch between the two source queries for each of these referenced queries.

 

So I need to switch this first part: "= #"SourceQuery1"" of each referenced query with the parameter input, but being rather new to M, I am having problems with the syntax, so initialy I just tried: "= ParameterName", which returns the current value of the parameter, but on the next steps fails, saying cannot convert value to type table.

 

So I know what the issue is, but I tried various variations of this part: "= ParameterName", but I can't seem to get the syntax right.

 

I am sure the solution is a simple one 🙂

 

/RSK

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

Hi RSK,

 

Are you trying to use query parameter to select data source? If you are , you can directly use "ParameterName", not "= ParameterName". For details, please refer to: http://biinsight.com/power-bi-desktop-query-parameters-part-1/.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

Like this?

 

let
    Source = AppInsights,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Browser"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Browser", "BrowserName"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "BrowserID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"BrowserID", Int64.Type}})
in
    #"Changed Type"

 

-------------------

 

AppInsights is my parameter name. It still gives me this error on the second step: 

 

Expression.Error: We cannot convert the value "AppInsightsSQL (Api)" to type Table.
Details:
    Value=AppInsightsSQL (Api)
    Type=Type

 

--------------

 

AppInsightsSQL (Api) is my default source query.

 

What am I missing here?

 

Thanks.

Hi RSK,

 

"Expression.Error: We cannot convert the value "AppInsightsSQL (Api)" to type Table.
Details:
    Value=AppInsightsSQL (Api)
    Type=Type"

<--- Modify Code as pattern below and check if it can work.

Source = Sql.Database(InstanceNames, Databases, [Query = "Select * from *"])(e.g.: InstanceNames and Databases are parameters)

Regards,

Jimmy Tao

Anonymous
Not applicable

Thanks Jimmy, but as stated in my original post, only one of my sources is a SQL db, the other is an Azure Table Store. So that is why I need a parameter to switch between those two source queries regardless of the original sorce type. Hope it makes more sense now.

Hi RSK,

 

So your requirement is to change data source within different data connectors, right? Query parameter can't switch from one data connector to another, to change data connector, you should click "Get Data"-> "Azure Table Storage". After that, you can use query parameter to select connection info(e.g.: servername, instancename, web url, header).

 

Regards,

Jimmy Tao

Anonymous
Not applicable

Yes I know how to swith connector manually, but I want from my referenced query, to be able to switch between two different source queries using a parameter, regardsless of those two source queries connecting to two different source-types (SQL and Azure Table) initially.

 

Is it not possible to parameterize which source query a referenced query is pointing at?

 

Thanks, and sorry if I am not making myself clear.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.