Use query parameters to define view headers in REST API calls and expanding new columns

[angeloola]

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 

So, the new table column is retrieved as a result of the user inputs, however it doesn't flow through the original 'Expand Columns' step to repopulate the report. Refreshing the report keeps the old column until it is selected in the Query Editor and Re-Expanded again, then 'Close and Apply' This NEEDS to flow through automatically. HELP!

[v-juanli-msft]

Hi @angeloola

Could you find a note as the screenshot below

After editing  parameters, this message occurs, you need to click on "Apply changes" so that the change can be applied to the data model.

Additionally, according to this article, Note that:

Parameters are just like any other query in Power BI Desktop; they can be referenced from other queries, loaded to the Data Model and reference in DAX expressions, etc. By default though, Parameters are not loaded to the Data Model, so users have to right-click on a parameter and select “Enable Load” if they want to have them loaded to the Data Model.

Best Regards

Maggie

[angeloola]

@v-juanli-msft Thanks for the reply Maggie. I do select 'Apply Changes' and because the new table column I am selecting has a different name than then one previously, it seems that the table doesn't want to update on 'Apply Changes'. The article you mentioned related to modifying filters not table column selections. Using a parameter to select the data column that gets built into the table works fine, but I have to go back into Query Editor, and re-do the last step of parsing the table in order to have the new column take effect.

This may be a fundamental limitaton of the Query Parameters when being used to select what table columns are pulled in from a RESTAPI view. Please let me know if you have any additional thoughts. My entire workflow plan was based around letting clients choose the table columns. I'm sure there must be a trick to getting the column to repopulate. Maybe requiring some advanced editing in M...??

Help, please!

[angeloola]

Here is my M query from Advanced Editor:

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 Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"company_name", "region_name", "country_name", "year", "tot_numb_of_contracts_entry"}, {"company_name", "region_name", "country_name", "year", "tot_numb_of_contracts_entry"})
in
#"Expanded Column2"

Ok, so the blue text represent the query parameters the user selects. The first 2 are FILTERS, the second one defines the column from the original view that is imported into the final table (along with company_name, region_name, country_name, year). The red text represents the selected column. I think because it displays a distinct value once parsed, instead of a function that represents the column name, it can't update in the report. Basically i need the red text to update dynamically in my report without having to manually go back into the Query editor and re-expand the columns.

Can I substitute the red text for some code that indiciates "whatever column the user selected from Company Stat"??

[angeloola]

** I meant the 3rd blue field represents a SELECTED COLUMN (not a filtered value)

[angeloola]

@MarcelBeug This looks similar to a post of yours I found from 2016. Can you help with this? Thanks! Angela