Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello Team.
Could you please help me!
I have a SQL query which is changing by parameters in to download data. How can I get an aggregated table which contains all the individual querys? Each query has approx30-50 rows I would like to see all individual report in an aggregared 400-450 rows table.
How it is possible?
My query is
let
parameters = {"pYear","pBe_bid","pCountries","pCroptype","pBg_code","pMaturities","pNo_of_cks","pCheckgroup_id","pCheckgroupname","pCheckgrouptype","pCheckgroup_filt_country"},
cycle = List.Generate(
()=>[index=0],
each [index] < List.Count(parameters),
(state)=>
let
output = Odbc.Query("dsn=DenodoODBC", "WITH FilteredTable AS (#(lf) SELECT DISTINCT#(lf) e1.trial_guid, #(lf) e1.trial_id,#(lf) e1.trial_crop_type_lid,#(lf) e1.location_guid, #(lf) m1.be_bid,#(lf) m1.material_guid,#(lf) e1.trial_entry_relationship_guid,#(lf) t5.code,#(lf) s4.meana as value,#(lf) t5.code as TraitId,#(lf) 'VALUE' as Type,#(lf) 'TraitValue' as Kind,#(lf) m1.highname#(lf) FROM trialing_daas.rv_bb_experiment_trial_entry_daas e1#(lf) LEFT JOIN trialing_daas.rv_bb_location_daas l2 ON e1.trial_guid = l2.trial_guid#(lf) LEFT JOIN trialing_daas.rv_bb_material_daas m1 ON e1.material_guid = m1.material_guid#(lf) LEFT JOIN trialing_daas.rv_bb_stats_daas s4 ON e1.trial_entry_relationship_guid = s4.trial_entry_relationship_guid #(lf) LEFT JOIN managed.rv_bb_trait_sdl t5 ON s4.trait_guid = t5.trait_guid #(lf) WHERE #(lf) e1.stage_lid > 4 #(lf) and e1.year IN (" & Text.From(pYear) & ")#(lf) AND e1.experiment_id /* #####place of EXTID */ like '%'#(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) AND e1.spirit_trial_status >= 5#(lf) and e1.maturity_group_lid /* #####place of maturity_group_lid*/ IN (" & Text.From(pMaturities) & ")#(lf) AND t5.code IN ('YGSMN','GMSTP','NHL_P','PLHTN','YSDMN','SDMCP') #(lf) AND s4.mnga IS NOT NULL#(lf) AND s4.meana IS NOT NULL #(lf) AND e1.trial_id like '%'#(lf))#(lf), GroupedFilteredTable AS (#(lf) SELECT #(lf) trial_id,#(lf) GROUP_CONCAT(DISTINCT highname) AS CheckGroupComponent,#(lf) GROUP_CONCAT(DISTINCT be_bid) AS CheckGroupComponent_be_bid_mix,#(lf) trial_guid,#(lf) location_guid,#(lf) trial_crop_type_lid,#(lf) code AS TraitCode,#(lf) ROUND(AVG(value), 2) AS average_value#(lf) FROM FilteredTable#(lf) GROUP BY trial_id, trial_guid,trial_crop_type_lid, location_guid, code#(lf) HAVING #(lf) COUNT(DISTINCT CASE WHEN be_bid /* #####place of be-bid*/IN (" & Text.From(pBe_bid) & ") THEN be_bid END) = /* #####place of number of checks*/ " & Text.From(pNo_of_cks) & " #(lf))#(lf)select #(lf) GFT.trial_guid, #(lf) GFT.trial_id,#(lf) GFT.trial_crop_type_lid,#(lf) l2.country_code,#(lf) GFT.location_guid,#(lf) GFT.CheckGroupComponent,#(lf) GFT.CheckGroupComponent_be_bid_mix,#(lf) NULL AS trial_entry_relationship_guid,#(lf) NULL AS material_guid,#(lf) GFT.TraitCode,#(lf) GFT.average_value,#(lf) 'AVG' as Type,#(lf) 'TraitGroupAggregateValue' as Kind,#(lf) '" & Text.From(pCheckgroup_id) & "' as checkgroup_id,#(lf) '" & Text.From(pCheckgroupname) & "' AS checkgroupname,#(lf) '" & Text.From(pCheckgrouptype) & "' as checkgroup_Type,#(lf) '" & Text.From(pCheckgroup_filt_country) & "' as country_filtering_conditions,#(lf) 'CCCCC_ Place of Trial_Crop_Type_lid_filtering_condition' as Trial_Crop_Type_lid_filtering_condition,#(lf) 'Group' as Kindgroup #(lf)FROM GroupedFilteredTable 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;")
in
[index=state[index]+1, output=#table(output,{"output"})]
)
in
Table.Combine(cycle)
Thanks in advance!
Tivadar
Hi Jenratten,
Thank you for your input! I'm going to make a more detailed description because I can't use your input immediatly.
I come back soon!
BR
Tivadar
Ps: Could I somehow connect you directly?
Sure, you can send me a private message, however, if we keep correspondence here it will allow other community members to learn from it.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Hello @Bontivad - 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:
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.
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
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
User | Count |
---|---|
23 | |
17 | |
14 | |
12 | |
11 |