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
angeloola
Helper I
Helper I

Select view header from REST API using query parameter

Ok, so I am customizing a URL for a REST API service to use query input parameters that limit the data pulled in. It works for filters (e.g. '&$filter=region eq '[user input parameter]') but when I try to add a SELECT statement to the URL to limit the data headers pulled in, the table doesn't parse correctly in my report. The "&$select=company_name,country_name,[user input parameter for header]" does work, but the change doesn't flow through the Expand Columns step to repopulate my report.

 

So, to rephrase, the problem is that when a user edits the parameters in the report, selects a new menu item from "select company stats" under 'Edit Parameters', the table in the report doesn't update. If I go back to my query editor, I can see that the change was indeed implemented into the URL query successfully, but the change doesn't get incorporated into the Expanded Column. I have to go back to Converted to Table, re-expand the columns to get the new column of data and then 'close and apply' to repopulate my report. This is not acceptable. I have to make it automatic.

 

So, how can I use this query parameter approach to define my table headers. Ideas? This table is WAY TOO HUGE to import all headers and clients needs to be able to choose from all of them for maximum flexiblity.

 

Thanks,

Angela 

User parameter changes END at Converted to Table step. The newly selected header doesn't flow through the Expand operation...User parameter changes END at Converted to Table step. The newly selected header doesn't flow through the Expand operation...

 

So, the table in my report doesn't update dynamically... HELP! Thanks! This is a biggie for me to figure out. There are loads of reports I'd like to format this way. 🙂

 

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@angeloola,

Please help to post the code in Advanced Editor of your query here and post a screenshot about how you create the parameter in Query Editor.

Based on your description, you are creating source parameters, we should change parameter value in Query Editor and then apply the changes to Power BI data view and report view.
1.PNG


Regards,

Lydia

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

@v-yuezhe-msft

 

Exactly! Source parameters. Sorry I'm not able to communicate my problem elegantly... new to all of this.

I want my clients to be able to change the "source parameters" in the REPORT ENVIRONMENT and have that change flow through to the new tables after they click 'Apply Changes'. I plan to create custom templates that allow clients to customize the API call based on their source parameter selection.

 

More thoughts?

 

Is this even possible??

 

Here's the Advanced Editor M:

let
Source = Json.Document(Web.Contents("https://...(edited out).../retrieve/" & "company_stats" & "?$format=json" & "&$filter=company_name like '%" & #"Company Name Contains" & "%' and year>='" & #"Select Start Year" & "'" & "&$select=company_name,region_name,country_name,year," & #"Company Stat")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{1}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"company_name", "region_name", "country_name", "year", "tot_numb_of_fields"}, {"company_name", "region_name", "country_name", "year", "tot_numb_of_fields"})
in
#"Expanded Column1"

 

 

So, the black bold text represent my "source parameters" that go into customizing the REST API call. The red text is the column header a user selects from the dropdown list that support the "Company Stat" parameter. I am using this parameter to curate the table columns, but the problem is that when they select a new parameter for "Company Stat" in the report environment, the change doesn't flow through... Is it a naming problem? Today I looked into dynamically renaming columns, or just somehow to code the "value selected from 'Company Stat'" to populate the column header (and the associated data of course...).

 

The view itself has over 150 columns and there is millions of rows. So, you can imagine why I need a workflow that allows users to pick from those 150 columns (depending on their need-of-the-day) as opposed to pulling them all in...

 

Thanks so much for responding!

I really hope I can get this sorted....

 

Angela

@angeloola,


Please check if Power BI templates meets your requirement.

Besides, after you create parameters in query editor, use the format " &Company Name Contains& " instead of " & #"Company Name Contains" & " to invoke the paramaters.

Regards,
Lydia

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

@v-yuezhe-msft - the article was the same as the other you posted (and yes, I had already read that prior to this post)... Unfortunately, no, it doens't solve my problem.

 

I just need to know whether I can use source parameters to define fields in my table (as opposed to FILTERING a table). For me at the moment, filtering works fine. But when I use parameters to define the headers in my table, the changes don't flow through into the reporting environment the same way.

 

Can anyone give me a "yes" or "no" answer to this question?? I was thinking that if I could dynamically rename the columns it might work... At the moment, when a new table column is defined by a new parameter selection, it gets stuck at the "Expand Columns" step in the Query Editor...

 

😞

 

Thanks,

Angela

@angeloola,

Source parameter is used to filter data, I don't think it can be used to dynamically change column name.

Regards,
Lydia

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

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.

Top Solution Authors
Top Kudoed Authors