Dynamic M Query - Pass SQL query as a parameter

[AK92]

Hi @Everyone

Looking for some expertise on a scenario that I'm trying to figure out. Any quick help is appreciated as this has an impact in the architecture.

Source: ADB - Tables

Connectivity: Direct query mode

Scenario:

-I’m developing a PBI report for source data validation based on multiple test cases. 

-Source data is being validated through test cases(ADF) and few other ETL steps 

-Each test case is on different columns/tables and conditions and is written in SQL 

-Failed records are stored in a table(ADB) with fields like qc_id, qc_type, etc.,.

Sample data:

qc_id	test_query
1	select col1, col10 from #table where sales < 0
2	select col3, col4 from #table where quantity is null

PBI Requirement:

User wants to see the dynamic table as output as per the slicer selection on qc_id.

Eg.,

Slicer - qc_id

When qc_id '1' is selected from the dropdown, the respective test query (select col1, col10 from #table where sales < 0) should be executed and return the specified columns

[Anonymous · Accepted solution]

Hi @AK92 ,

I create a table as you mentioned.

Then I create a new table and here is the DAX code.

Table 2 = 
ADDCOLUMNS (
    FILTER (
        'Table',
        'Table'[sales] > 0
    ),
    "qcid", 'Table'[qc_id],
    "col1_1", 'Table'[col1],
    "col10_1", 'Table'[col10],
    "sales_1", 'Table'[sales],
    "SalesFlag", 1
)

So when you use a slicer, it will give you what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.