undefined

[SidsDataAnalyst]

I have one Sheet with all the quantity in separate columns date-wise and the same Price in different columns per date.

How can i convert that sheet into the below format?

Someone told me to do the Unpivot first the pivot, I am getting the sheet into that format, but it had some performance issues.

When i am refreshing the dashboard with some new data my system is getting hanged.

Is there any simple solution?

[SidsDataAnalyst]

Thanks for your suggestion

[BA_Pete · Accepted solution]

Hi @SidsDataAnalyst ,

Looking at your screenshot, I'm not entirely surprised that you are getting performance issues. I think there's a few things at play here that are combining to cause you problems:

1) It looks like this is being performed on a Load/Transform sample file, therefore any transformations you make here will be replicated for every source file that you are importing using this function.

2) Pivot/unpivot is generally very resource intensive as it requires the entire table to be loaded into memory to perform this transformation.

3) Split column by delimiter, in my personal experience, is also a slow function, but don't quote me on this as I've no evidence to back this up.

As per point 1, you will be doing these resource-intensive transformations over and over for each source file, so unsurprising that you're getting issues.

The best advice I think I can give is to try and move as many transformations as you can to your final combined query, rather than doing them multiple times on load.

At a glance, I would say you could probably move steps like 'Split Column by Delimiter', 'Added Conditional Column', 'Replaced Value', and 'Reordered Columns' to the post-combine stage. It appears as though 'Unpivoted Colums' will need to stay as this will likely be required to perform a clean append.

It's still going to be slow as you will be performing these steps on the combined number of rows, but at least you're only calling each function once, rather than X number of times.

Pete