Dynamic data importing from a database using Native Database Query (Power Query)

[Anonymous]

Hello everyone ,

I have an question I import data using Native Database Query (Power Query) for particular Year 2024 , is there a way how to make Pwer Quary dynamic? If so, what would I need to do so?  The "EventDate" values??

 I have it for 2024 but I would like if a user would be able to change Year 2023 or 2022 and so on.... 

[rajendraongole1]

Hi @Anonymous - Open Power BI Desktop and go to Power Query Editor.
In the Home tab, select Manage Parameters > New Parameter.
Name the parameter, for example, YearParameter.
Set the Type to Whole Number and provide a Current Value (e.g., 2024). This value will act as the default.
Optionally, you can provide a list of possible years that users can choose from (e.g., 2022, 2023, 2024, etc.)

You can try the below parameter in select:

SELECT cec_0.[CecCaseId], cec_0.[CaseId],
(SELECT CAST(CASE WHEN EXISTS (SELECT * FROM CecRepetitiveCase WHERE LeadingCecCaseId = cec_0.CaseID) THEN 1 ELSE 0 END AS BIT)) AS [HasClones],
cec_1.[RegisteredNo], cec_1.[Stateid],
cec_2.[StateOfProceedingName]
FROM [dbo].[CecCaseDetail] (nolock) AS cec_0
INNER JOIN [dbo].[CaseDetail] (nolock) AS cec_1
ON cec_1.CaseID = cec_0.CaseId
INNER JOIN [dbo].[CecStateOfProceeding] (nolock) AS cec_2
ON cec_2.StateOfProceedingID = cec_0.StateOfProceedingID
LEFT JOIN [dbo].[DGIIAgent] (nolock) AS cec_5
ON cec_5.AgentID = cec_0.PrincipalAgentID
WHERE EXISTS (SELECT * FROM [dbo].[CecCaseEvent] zz
WHERE zz.CecCaseID = cec_0.CecCaseID
AND zz.EventID = 1
AND zz.EventDate >= CONVERT(DATETIME, '01/01/" & Text.From(YearParameter) & " 00:00:00', 103))
AND EXISTS (SELECT * FROM CecLeadingCase zz WHERE zz.CecCaseId = cec_0.CecCaseId)
AND cec_0.StateOfProceedingID IN (2)
AND cec_1.RegisteredNo <> '0'
ORDER BY cec_1.RegisteredNo

[Anonymous]

Hello Rajendraongole1,

Thank you for respone, where to put your amended code??  I tried to implemented your solution, but how it works now... I would like a user change year.. visually. 

[Anonymous]

Thanks for the reply from rajendraongole1 , please allow me to provide another insight:

Hi, @Anonymous 
 

Based on my understanding, the solution that rajendraongole1  is suggesting is set up as follows:

1.Firstly, modify the parameters in Power Query. Here is my reference example:

2.Secondly, click on Advanced Editor to modify the M language. My example is as follows:

As we cannot directly access the data source, you will need to adjust the specific code according to your particular circumstances.

Best Regards,

Leroy Lu

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