This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Here is a fun way to do this. I've been getting into the Developer tools a lot recently, so this solution uses several of them to dynamically grab columns and rename them for you.
I'm getting a list of the field names, then grabbing the last two and first field names, and using these to update the metadata description fields for these columns. The Dynamic Select tool now only keeps fields where the metadata description field is not empty. I then use a Multi-Field formula tool to update all fields with the exception of "Resource Name". And then finally use another Dynamic Rename to update the field names to Prior and Current Period to match your output.
I started to experiment with this approach out of necessity. There are times when you will find yourself working with a very large dataset and that transposing the data drastically decreases performance.
Interesting 🤔 definitely a valid point and Amazing 🙂 how you are able to tackle it. Looking forward to exploring and learning a lot more from you 😀
A suggestion you can turn dynamic rename warning messages by toggling below. Some people are bothered by warning messages 😅 this it result window would look clean. And doesn't unnecessarily make you worry.
@atcodedog05 I might write up a post on this at some point, but the applications to this method are pretty robust depending on how creative you get with it. 😁
I'm currently using it similar to the solution I posted, though I've also used it to dynamically start my import of data files that fluctuate the starting row/columns.
I also just wanted to post quick the performance improvements between the transpose and dynamic select methods. For this experiment, I copied the sample data down to a little over 1 million rows. The transpose method ran in 1:15 minutes vs 10.3 seconds with the dynamic select method!
I did a similar performance test to check after seeing your solution 😅. I was totally amazed by the performance boost of dynamic rename and dynamic select method. I have used these steps before in bits and pieces. But never had the necessity for any usecase or thought of building a powerful solution like this 🙂. It's definitely a creative solution for sure 🙂.