cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
angeloola Regular Visitor
Regular Visitor

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.jpgUser 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. Smiley Happy

 

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Select view header from REST API using query parameter

@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.
angeloola Regular Visitor
Regular Visitor

Re: Select view header from REST API using query parameter

@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

Moderator v-yuezhe-msft
Moderator

Re: Select view header from REST API using query parameter

@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.
angeloola Regular Visitor
Regular Visitor

Re: Select view header from REST API using query parameter

@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

Moderator v-yuezhe-msft
Moderator

Re: Select view header from REST API using query parameter

@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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 27 members 971 guests
Please welcome our newest community members: