One of the most useful features is unpivot, which is what we are covering in this post. But Power Query has many features to make this transformation easy. So what is the correct layout data for Excel to use? Check out this post which provides a good answer to that question.īefore Power Pivot, it would take us a long time to transform data we had to resort to complexity or long VBA codes. But I have found that getting data in the correct format I have found these advanced skills are rarely needed. I picked up a lot of tips and tricks by trying to learn these same techniques. I used to be amazed at people who could use complex formulas. But if the data is in the right structure, Excel becomes easy. If the data layout is wrong, you be continually fighting Excel having to use advanced formulas, adding unnecessary columns or even duplicating data. Preparing data is probably the most critical success factor in data analysis.