Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Bontivad
Frequent Visitor

Create a merged report from SQL queries reports which are based on parameters changes

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

 

4 REPLIES 4
Bontivad
Frequent Visitor

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

jennratten
Super User
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:

  • 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.

jennratten_0-1717354316956.png

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.    

jennratten_1-1717354379886.png

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.

jennratten_2-1717355279844.png

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.

jennratten_3-1717355339661.png

 

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.

Bontivad_0-1717507108223.png

checkgroupname list

Bontivad_2-1717507357053.png

 

From these list I created the parameters. 

Bontivad_1-1717507231038.png

 

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

 

 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors