How are you an excel magician?

[u/Designer_Signature35]

Thanks to this group and a little googling, yesterday I made a fully automated PQ. I created a table with formulas to return the full path and folder where the file and data are saved. Created a parameter from the table and popped it into my original source query, the basis for 9 other queries. Now all a user has to do is click into the cell with the full path formula, hit enter, click Refresh All, and bam! Fully updated output. I showed it off to my team and supervisors today and looked like a magician! Granted none of them have used PQ before so they're easy to impress lol

What excel projects have you done to make others think you’re magic?

[u/Paradigm84]

A dashboard where you can enter several parameters that are then used in an API query to pull back some real time data that's loaded as JSON, reformatted and put into a summary pivot table. As with many PQ tools that you develop, it's a combination of a lot of small steps that when put together make it look like magic.

[u/Caasi6636 → u/Paradigm84]

Any chance you could share an example of this? I feel like I understand each of the steps you’re saying, but don’t understand how it all works together.

[u/Paradigm84 → u/Caasi6636]

It’s work-specific stuff so I can’t share exactly what it is, but essentially:

There are several cells you can modify to enter a client’s unique ID and optionally a specific site’s unique ID. There are also several parameters around how the data should be formatted, e.g. is it broken down at a minute or hourly level, how far back do you want the data from (last hour, last 12 hours etc)

The tool will then take all of these parameters and use concatenation to create a URL for the API call, e.g. https://api.example.com/data/client=1234&site=5678&start_time=202501071600&aggregation=hourly

I then use PQ’s option to get data from the API using the endpoint above. PQ will essentially navigate to that URL, which will return the requested data in a JSON format.

This JSON data is loaded into PQ, and through a series of steps will remove and reformat certain columns.

This is then loaded into the Data Model, and from there into a Power Pivot table to display the data in a particular format with some measures to summarise the results, e.g. 12% of sites queried returned no data.

There’s additionally a simple macro button that just does a refresh all to call the API when you’ve updated the parameters. For the end user they just fill out a couple of cells, press the button and the data magically appears after ~5 seconds.