I have a dataset currently in the structure of the first table in the file attached.
I want to rearrange this into the structure of the second table. Note that not every Product is referenced for each of the 3 different Sizes, but I want to create the full list of distinct Products that exist across all Sizes and all Years.
Is there a way to automate copying the data from the first table structure into the second?
P.S. the full dataset I'm working with has 15 years and 70 Products so not feasible to do this manually.
How about the attached very basic Power Query solution?
The result is very similar to what you described, but I believe it contains all you need. A little more M-coding and the order Large, Medium, Small can be reversed to how you may want it.
That looks great, Riny, thank you.
I've never used Power Query, and not sure what M-coding is.
How would I go about implementing that solution?
Oh, if you are completely new to Power Query (PQ) and M (the code language that runs it), it may not be that easy. You need som basic knowledge first. You'll find many great blog posts on PQ on this site. For example this one:
https://www.myonlinetraininghub.com/get-started-with-power-query
In my example I called the bleu table "tblSizes" and PQ did the rest. You could insert enough columns between the blue and the green table and paste your real data (70 rows, 15 columns) in tblSizes. Then, on the Data ribbon press 'Refresh All' and see what happens with the green table.
If it works and you can live with the column sort order as it is, you're done. And then start learning more about PQ 🙂
Thanks again, Riny. I've been meaning to learn about PQ for some time, so now I have a good incentive to get started.