Hey all - I posted on one of the wonderful YouTube videos and was directed here by Mynda herself, yay 😉
I have a dataset that has a weird layout because the data entry portion needs to be semi visual and look a certain way. I know the raw data can be changed to make this easier (Which I have already done as a workaround) but the format I used was desired by the users because the raw data is used for presentation as well as creating connections/error-refreshing-connections-dashboard-reports.
It's hard to explain so I have made a sample with fake data with some explanations in it. Basically it's multiple sets of data that are formatted into a grid type set of boxes. You'll see in the file.
If anyone can help it would be appreciated. I included in the file the workaround I used just so you know that I did put in the work. I tried using PQ with my dataset but I could not get it to the desired output.
Thanks in advance 🙂
Hi James,
The attached file contains a rough query that creates a raw data table, structured like the WORKAROUND table you did in Excel. Key is that you name each of the six data ranges. I assume you'll be able to figure out what I did in and take it further from there, based on what you developed already.
Best,
Riny
Thank you - sorry for the delay in replying, have been swamped. Going to check this out! Appreciate you.
Edit:
Ok this worked except for one huge problem. In my workbook there are multiple sheets with the same layout for the data. Around 50. If you look at my end data you notice there is a name on the left column "Name here" that name should match the Sheets name. Instead of including tables in the data I had included just the sheets and then expanded the data.
I may be able to use what you've done to piece something together..but not sure if I will be able to figure it out. I think I should have included another sample file with my solution and query, showing more than one sheet.
Since there are 50 sheets with the same ranges they can't have the same unique names..but maybe if I use PQ filters I can filter it to anything containing those names (because I think if you duplicate names in a sheet it keeps the names and adds sequential numbers?)
Will ponder further. Sorry I didn't explain myself well in the original post. But TLDR: There will be multiple sheets with Users Names and those must all be included in the end data (and the name needs to be in the leftmost column of the data like in my example output.
