Hi everyone
I am slowly learning Power Query and can see it being very handy for building projects. Oftentimes I deal with changing construction design information (i.e. issue 1 of the architects drawings vs issue 2 of the architects drawings). When issue 2 of the drawings supersedes issue 1, I need to calculate the difference between what was meant to be built originally and what is meant to be built now.
I'd like to be able to use Power Query to manipulate a long and flat table into a Pivot as shown in the attached excel. The initial table is formatted in a helpful way to allow the initial data entry, but to actually get insight from the data I require to be reformatted in such a way I can view it as a Pivot.
Is what I am proposing in the attached excel feasible with Power Query? If any one could give me a steer how to achieve this I'd be grateful.
Thank you
Tom
Hi Tom,
You can certainly to this with Power Query. See the queries in the example file attached with the 'Append1' query showing the final result.
Hope that helps point you in the right direction.
Mynda
Hi Mynda, this is excellent thank you. I see that your solution involves breaking the table up into two tables which is very clever.
Hi Tom,
Mynda's solution of splitting the raw data in two tables inspired me to come up with a single query that basically performs the same steps, though on a set of grouped tables. The query is directly loaded into the pivot table that starts in J50 in the attached file. Same as yours, though the sorting is a bit different.
Riny
