I have a table that has userid and project id going down, and month going across, with 3 values per month.
Picture something like this:
userid project id jan actl hrs jan fcst hrs jan pct feb actl hrs feb fcst hrs feb pct
aa123a proj1 12 5 20% 41 30 30%
aa123a proj2 14 7 18% 45 40 35%
bb123a proj1 5 1 1% 17 25 19%
bb123a proj3 55 50 28% 33 27 17%
I could unpivot the whole lot, but my problem is that I want the hours formatted as integers, and the percentages formatted as percentages.
I don’t know how to make that happen with simple attribute/value pairs resulting from unpivot.
Alternatively, I thought about making the data look like this:
userid project id month actl hrs fcst hrs pct
aa123a proj1 jan 12 5 20.00%
aa123a proj1 feb 22 30 30.00%
aa123a proj2 jan 14 7 18.00%
aa123a proj2 feb 33 40 35.00%
bb123a proj1 jan 5 1 1.00%
bb123a proj1 feb 11 25 19.00%
bb123a proj3 jan 55 50 28.00%
bb123a proj3 feb 33 27 17.00%
But I don’t know how to make that happen using unpivot either.
@brixmitch
Dont see why this cant be done. Any chance you upload a sample of what the data is and then something that shows what you would want the data to be? It’s hard to see exactly in the data above.
There’s a parameter for the file location, be sure to change that. Also, not sure what the “% pct” column is, but generally those types of calculations are better in DAX vs. in Power Query/Calculated column
What a fascinating set of data manipulations! merge, transpose, uinpivot, split, merge, pivot…
I can honestly say I would never have thought of that on my own.
After a while you see the same basic patterns, just a different wrinkle here and there. And please don’t think for one second I did that all in one try. Took a few “lets see what this does” type of things.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
