Create Multiple datasets based on a few columns & Transpose some of data into Line listing

I cannot post Images or Links yet because this is my first post. Once I am allowed to post these I will return with the complete Post. I really need help here

This may be too complex of a question to ask.. I am going to give it a shot anyway , because I am stuck trying to balance a 360,000 + row linear dataset against time to deliverable, computational resource with formulas, the time it takes to process with queries and formulas ... and I kind of just want to throw my laptop in front of a moving train

Hopefully there is some function to achieve this. Otherwise it is all manual work for thousands of repetitive processes.

I have database I need to restructure and split multiple times based on a few key parameters.

I have attached a representation of the dataset (Truncated & deidentified, lol)

** My goal here is to achieve TABLE2 from TABLE1 SUB-Goals: Efficiency, CPU load minimum, Time to refresh = minimum **


Table 1 (Source data) and Table 2 (Resulting Tables)


There are 17 Columns in the Table 1 example, but since this data is linear, and the number of QUESTEXT can vary in number, the resulting Table 2.1, 2.2, etc's number of columns (Bottom 2 tables) will vary, so it is not important to conform to any set # of columns in the resulting tables.

Basics Idea:

I want to take TABLE1 & separate the subset datasets into their own tables, without having to do this manually thousands of times.

I was thinking of using some advanced grouping in Power Query, but I am not certain this is the most efficient method & I am not very proficient in "Group-Pivot" process. Plus, Grouping and then Pivoting usually ends up in a single table with varying # of rows. So, I would then have to separate them manually or use a named range manually, which I would like to avoid

The datasets would be identified, separated & Sorted by the following (BLUE) by PAGENAME, adding a single line per SUBNUM. Oh, and there are 30 additional VISNAME as well, so "Screening" is not the only one, it is 1 of 30.

So a single table will have a single line per VISNAME & SUBNUM (Attachment 2)

The data under (GREEN) COLNAME become the headers (in order of CTRL ID)

The Headers in (ORANGE) become the values under their respective SUBNUM & Headers

Then for URL (PURPLE), basically copy down to the next URL, as it will repeat for each combination of (SUBNUM, VISNAM, PAGENAM)

Thanks to anyone who has any good ideas. I am in need of something that doesn't crash "regular" company laptops.