Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 'odd' data in PowerBi.
What I mean is its super wide (243 columns) and it was based on repeated measures in a redcap survey instrument (kinda looks like this:)
You can see the columns (pi_1,cc_1, etc...) come from an instrument that looks like this:
Well I want to display those values in a Power Bi dashboard thats shaped similarly to the original redcap instrument, here's the beginning of my attempt:
My goal is that when you select "May" and "Erin" like I did in that dashboard, you'd get her results from that page/month as it was displayed in Redcap, like so:
My current strategy isn't working great for several reasons including:
Is there a better way? A different visual? A better way to group? I know that's vague but I dont know what I dont know.
So to recap/summarize: How can I best display a dashboard that looks similar to what was in Redcap?
Thank you. I think this is definitely down the right path but I am REALLY struggling with the specifics of it. Let me elaborate:
In my raw query, every row is a month and person, like so:
(I've renamed some columns from the question above)
And there are repeated columns of pi, cc, st, sp, eb, ep, pe (with _1, _2, _3 etc.. from the original instrument in redcap)
In the file report I'd like to make (whether Power Bi/ excel/ etc) I'd like to be able to select a month, select a person, and then have a view that looks like this:
Cost Center (was cc_#) | Short Title (was st_#) | Budgeted % (was eb_#) | Actual % (was pe_#) |
123456 | study | 5% | 6% |
654321 | other study | 28% | 25% |
So i'm kinda stuck here figuring out WHICH columns to unpivot/pivot. I've tried EVERY column from pi_1 to pe_29 and I get this:
Which kinda makes sense to me. I was thinking I could rename the values to drop the _# and then I could pivot them. But then the problem is I get multiple values per month/per person that have the same value and the pivot errors. Its also kind of important that the cost center for cc_29 stays with other _29 values.
So in other words, I'm lost. I agree the pivot/unpivot may help (and I appreciate the guidance), and I know what I have now and where I'd like to go..... but I'm totally lost trying to figure it out.
Will share anonymized file content if useful in a link or something.
Hi @JoeCrozier ,
According to your description, you can convert the imported data in power query. Refer to the following document about unpivot columns.
For more details, you can read below document:
Unpivot columns (Power Query) (microsoft.com)
Pivot columns - Power Query | Microsoft Docs
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |