Forum Discussion
Create a merged report from SQL queries reports which are based on parameters changes
Hello Anonymous - Providing samples of the data and an example of the expected result makes it significantly easier for us to provide meaningful feedback here. That being said, I'll do my best. Note, the solution described below is intended to demonstrate the general sequence of events, or pattern, that would be needed in order to repeat a query for each combination of parameter inputs and then combine the results into one table. I have attached a pbix which contains the example outlined below. I hope this helps!
Assumptions:
- Each time the query is executed with different parameter inputs, the resulting table has the same column names, data types, and always has at least one record.
- You have table which will be used for inputs. One query should be executed for each row of the table.
- You want to combine the results into one table.
Create one table which contains one row for each set of parameter values.
Create a function which contains the logic to be performed. In this scenario, the function is getting the rows from the Opportunities table for which the values of the AccountSeq column matches the AccountSeq value specified in the parameters table. The data table does not have to exist in the pbix, it was just added here to make for an easier example.
This function is retrieving the records from the DataTable for which the specific column values match those that are in the parameters table. This version is only looking for one column value.
fn = ( ParameterTableName as table, DataTableName as table, ColumnName1 as text ) =>
Table.AddColumn(ParameterTableName, "Result", each Table.SelectRows(
DataTableName, (x)=>
Record.Field ( x, ColumnName1 ) = Record.Field ( _, ColumnName1 )
)
)You can easily increase the column to be evaluated like so....
fn = ( ParameterTableName as table, DataTableName as table, ColumnName1 as text, ColumnName2 as text, ColumnName3 as text ) =>
Table.AddColumn(ParameterTableName, "Result", each Table.SelectRows(
DataTableName, (x)=>
Record.Field ( x, ColumnName1 ) = Record.Field ( _, ColumnName1 )
and Record.Field ( x, ColumnName2 ) = Record.Field ( _, ColumnName2 )
and Record.Field ( x, ColumnName3 ) = Record.Field ( _, ColumnName3 )
)
)Invoke the function, which is executed for each row of the parameters table, creating a new table with the results.
Expand the results to get your combined table. In this scenario there was just one row of data for each parameter table row. However, there are multiple rows returned, they would also appear here, in the combined result. No changes to the process would be required.
Hello Jennratten,
(I need some more hint because I'm a beginner in Power query and Power BI.)
I try to give you the description as precisly as I can. Maybe I'm working on a bad solution.
I have a "Parameters.xls" input table from xls. I made from each field a list PBI. than I created a parameter from each (e.g. Years, checkgroupname etc.). I have 11. Each row is in the sequence of the original input file. each time when I wish to use the query I want the to use the same row number of the parameter like you did in your example table by AccountSeq. but here I can't add the sequence number.
checkgroupname list
From these list I created the parameters.
By these paramaters I tried to change dynamically and merge the original query, which is targeting a ODBC database.
My paramters are look like in the Advanced editor in PBI:
parameters= {"pYear","pBe_bid","pCountries","pCroptype","pBg_code","pMaturities","pNo_of_cks","pCheckgroup_id","pCheckgroupname","pCheckgrouptype","pCheckgroup_filt_country"},
Than I have the original path of the data source: which is anODBC query:
Odbc.Query("dsn=DenodoODBC", "WITH FilteredTable AS (#(lf) SELECT DISTINCT#(lf) e1.trial_guid, #(lf)e1.trial_id,#(lf) ............... GFT#(lf)LEFT JOIN trialing_daas.rv_bb_location_daas l2 ON GFT.location_guid = l2.location_guid#(lf)ORDER BY GFT.trial_id ASC;")
Than I changed the query sections of the filtering "AND" conditions for the desired paramters:
e.g. and e1.year IN (" & Text.From(pYear) & ")#(lf)
AND m1.be_bid /* #####place of be-bid*/ IN (" & Text.From(pBe_bid) & ")#(lf)
and l2.country_code /* #####place of country_code*/ IN (" & Text.From(pCountries) & ")#(lf)
AND e1.trial_crop_type_lid IN (" & Text.From(pCroptype) & ") #(lf) etc.
(see my full code in the original mail)
So I have no final dataset which can be downloaded to the desktop than filterable. I thought the ODBC database could be filtered by parameter change than can be merged.
I need to merge these paramter filtered rows into a big table. Is it possible?
Thanks
Tivadar