Hi,
I have read the multiple parameter topic but I am not sure whether it can be applied to my case.
I am hereunder describing what I have already done.
Starting from two queries based on two different excel tables I have filtered the first one by a varying parameter and then I have merged it as new to the second one. The merge is accomplished with a function that extracts data and load them to a new excel sheet every time. This second part with the function works ok and I can create as many queries as are the number of parameters. The bad side of my solution is that is that change of parameter is manual.
Moreover the issue comes when I want to refresh all queries together. Unfortunately being the functions and the parameters not coherently linked and being the parameters not variable with the variables in the envoked function queries, I have to change the parameter manually and the refresh the function queries one at the time.
Not easy to explain but I hope that the attached files might explain better than me what I reached so far and what I would like to obtain 🙂
The PQ file is "Extract data with Param&Functions.xlsx"
Thanks a lot for any support!
Alberto
Hi Alberto,
Thanks for sharing your files, this makes it much easier to understand, although I'm not 100% sure I've got it.
It looks like you're trying to generate a separate table for each LTP. If so, I wouldn't use Power Query for this. Power Query's job is to clean and consolidate data.
There is a great PivotTable tool that will automatically create those sheets for you as explained here.
I hope that points you in the right direction.
Mynda
Hi Mynda,
thanks for your reply. I will follow your nice advise to complete the second part of my task.
Unfortunately in my case, before extracting data in various sheets, I need to do to a little bit of cleaning and then I need to filter the LTP (one at the time) in the first table with PQ and then I need to merge the consolidated data with the second table again with a little bit of cleaning in PQ. Then I could extract data.
I don't know whether I was a little bit clearer this time. At least I hope 🙂
Thanks again,
Alberto
Hi Alberto,
You can do the cleaning and merging in Power Query and then use the PivotTables to split the data out into separate sheets for each LTP. i.e. create the LTP Master Line List up to the Rimosse altre colonne step, then Merge it with the Linee su PERT query. Close and Load to a table that you can use for the PivotTable and use the 'Shoe report filter pages' technique I referenced earlier to create each sheet. See example attached - note, you will need to change the file paths in the queries as they are referencing my PC.
Mynda
Hi Mynda,
sorry if go ahead with explanation but unfortunately it is not so straightforward the point as my job, though. 🙂
As I have seen from your kind file you have filtered all the LTP in one shot. Unluckily for duplicate reasons in the origin that I need to keep, I have to do the filtration step one LTP at the the time and then merge to the second query and then extract data. That's why I introduced the Parameter. But most likely as I understand there is no chance of doing my steps in a fully automated way. The only solution i can think about at the moment is to create 2 queries to be merged per each LTP. In this way pivot table is not necessary any more.
Hope this helps to transfer my issue.
Alberto
Just to add I want to share my last file. Like this seems to be working. I tried moreover as final touch to create a function to be evoked per each LTP but with function introduced I receive the Formula Firewall error while in the queries without it, it is ok. I don't why. Maybe you can advise.
Alberto
Hi Alberto,
Session 7.05 covers formula firewall errors.
Mynda
