Forum Discussion
Seedling distribution
ms_mit Great you could get it to work. And what's 15 minutes for a job that would otherwise take perhaps a whole day of copying and pasting. Now that I look back at the query, I see I was over-thinking the matter by adding the Grouped Running Total. We can do without and it will probably speed up the process. See attached.
Thanks again!
ms_mit Well, if you are completely unfamiliar with PQ, this is not very straight-forward. The file I uploaded has quite some "non-stadard" coding, and is not really a finished product. Not sure if you are going to master it if you are in a hurry. What you could try is take the attached file and dump your real data in the structured table called "Table1" with exactly the same column names. Then press Refresh on the Data ribbon and see what happens.
More about the PQ basic here:
https://exceloffthegrid.com/power-query-introduction/
Thanks Riny, This gets me on the road to the solution. I have used PQ in the past for some simple aggregation of data from multiple worksheets but nothing like the transforms you are doing here. Admittedly, I don't understand the queries you created, especially the steps in the Append1 and what the GRT function does but I will eventually figure it out.
I did run this query with a full dataset. Takes about 15 minutes to run even with a relatively powerful computer. But it works. Now I just have to figure out how to transpose the species and seedling count for the mixed bags.
Thanks also for the link to the PQ tutorial. It looks like a good one.
ms_mit Great you could get it to work. And what's 15 minutes for a job that would otherwise take perhaps a whole day of copying and pasting. Now that I look back at the query, I see I was over-thinking the matter by adding the Grouped Running Total. We can do without and it will probably speed up the process. See attached.
Riny_van_EekelenYes it works great.For some reason it is filling a lot of seemingly redundant rows with empty Qtys but this is easily filtered out.
For sure it would have been more than just one day of cutting and pasting to assemble this list. The finished list has around 70,000 rows. It has also showed me that we have considerable non-unique Farmer Names. But this is also easily fixed.
Thanks again for your help with this Riny.