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.
ms_mit Since you mention to have 1.3 million seedling to distribute to 8000 farmers, you should look into Power Query. Played around a bit with your example and came up with a "solution" that needs refinement. Half of it is in PQ. For the other half I cheated a bit by adding some manual columns in Excel. Should be possible to do those in PQ as well but have the inspiration right now.
HiRiny_van_Eekelen I had a look at your PQ setup and I have to admit that for now it is over my head but I will come back to it and learn it. In the mean time, I wonder If I could ask you if you have any ideas for an alternate solution. I am using this data as the basis for printing labels for all the bags of seedlings and it would be more workable to put the species in the shared bags in a single row, rather than more rows of the same bag number. I have attached a file with the structure I am talking about. If it you have a quick idea how to do this, I would very much appreciate it.
- Hey Riny, That is exactly what I need. Thank you!!! Helper columns work fine for me. This is a thing I need immediately so, if it works, it's good. I can figure out the bits with the helper columns. Can you please point me toward where to open your bits of PQ? I need to study how this works.
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.
