Power Query - Custom SQL, allow end user to change date range

[denzo36]

Hi all,

One of the more annoying tasks of my job is running ad-hoc reports from SQL server that need to be refreshed on say a weekly, monthly, yearly etc. basis. This is usually just a simple data extraction of data without too much manipulation going on post extraction.

What I tend to do is run the query through power query below, that way when I need to refresh I can refresh the query. This then gets sent onto my boss or whoever is requesting the data.

What I'd like to do is allow the end user to be able to run the query themselves, without the need for me to manually refresh and then send it to them.

Is there anything like a custom function in power query that could created that would allow the user some sort of on screen interface where they can adjust the date range of the query themselves that would run the query for the data range they choose?

This seems like a long shot I know.

Thanks in advance.

 

[alansidman]

you could move the file with the PQ in it to their machine or to a shared drive. In Queries and Connections you can set PQ to refresh upon the opening of the Spreadsheet.

 

[denzo36]

That could work, thanks for your response

 

[Kyle123]

USing Microsoft query would work too, we do this for our accounts department. Copy and paste the SQL, create variables in MSQuery and link them to cells. They can change the dates in the worksheet and just refresh the table

 

[denzo36]

USing Microsoft query would work too, we do this for our accounts department. Copy and paste the SQL, create variables in MSQuery and link them to cells. They can change the dates in the worksheet and just refresh the table

Thanks, our office doesn't have that at the moment unfortunately but will keep it in mind for the future

 

[Kyle123]

It's built into Excel, it's what existed before the whole Get and Transform stuff

 

[denzo36]

It's built into Excel, it's what existed before the whole Get and Transform stuff

I've never seen that before, are you talking about Access? If not, could you do some basic screenshots?

 

[jdellasala]

Regarding allowing the user to adjust the date range, you could give them named range cells to enter the start and end dates:

Book1
	A	B
1	Date Start:	01/01/2023
2	End Date:	03/15/2023
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TODAY()

Cell B1 is named StartDate, and cell B2 is named EndDate. They can be brought into Power Query without having to turn them into tables:
Power Query will bring them in as a table with one column (Column1) and one row with the date in the single cell.

let
    Source = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    Column1 = ChangedType{0}[Column1]
in
    Column1

The ChangeType step is needed because the simple date will be brought in as a DateTime data type. Then right click on the date (not the column) and select Drill Down. The Queries will have the same name as the Named Range - StartDate and EndDate in this case.
Those names can now be used in your SQL queries. I'm not familiar enough with SQL, but in M Code you could turn

= Table.SelectRows(#"Changed Type", each [Date] >= #date(2023, 2, 1) and [Date] <= #date(2023, 3, 15))

into

= Table.SelectRows(#"Changed Type", each [Date] >= StartDate and [Date] <= EndDate)

with the same result.
Of course, if they change the date, they'll have to learn how to navigate to the Refresh All button and click it - or save the file and re-open it! The Refresh all could also be added to the QAT.
There's plenty of other embellishments possible - making the cell Data Validation lists, breaking the date into three cells for each date (Day, Month, Year) and assembling the named date cell from those three cells, and plenty others!
Hope that helps.

 

[denzo36]

Regarding allowing the user to adjust the date range, you could give them named range cells to enter the start and end dates:

Book1
	A	B
1	Date Start:	01/01/2023
2	End Date:	03/15/2023
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TODAY()

Cell B1 is named StartDate, and cell B2 is named EndDate. They can be brought into Power Query without having to turn them into tables:
Power Query will bring them in as a table with one column (Column1) and one row with the date in the single cell.

let
    Source = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    Column1 = ChangedType{0}[Column1]
in
    Column1

The ChangeType step is needed because the simple date will be brought in as a DateTime data type. Then right click on the date (not the column) and select Drill Down. The Queries will have the same name as the Named Range - StartDate and EndDate in this case.
Those names can now be used in your SQL queries. I'm not familiar enough with SQL, but in M Code you could turn

= Table.SelectRows(#"Changed Type", each [Date] >= #date(2023, 2, 1) and [Date] <= #date(2023, 3, 15))

into

= Table.SelectRows(#"Changed Type", each [Date] >= StartDate and [Date] <= EndDate)

with the same result.
Of course, if they change the date, they'll have to learn how to navigate to the Refresh All button and click it - or save the file and re-open it! The Refresh all could also be added to the QAT.
There's plenty of other embellishments possible - making the cell Data Validation lists, breaking the date into three cells for each date (Day, Month, Year) and assembling the named date cell from those three cells, and plenty others!
Hope that helps.

That is awesome, thank you!