Search code examples
powerbiredcap

Power Bi dealing with repeated instruments from REDCap

I have data like this:

enter image description here

It comes from REDCap, and as you may be able to tell, the data in the far right columns are repeated variables about each "protocol_title" (the far left column). I.e. "Love it" and "I want a disc instead" are both about "study 2"

enter image description here

I've imported the data into Power Bi and currently I have this:

enter image description here

What I'd like is for the top left visual to only have one row per study (with columns such as principal investigator and method of image transfer, i.e. columns that had data in the first row) and a visual on the lower left with all the right-most columns.

By switching the top visual from a table to a matrix I can kinda accomplish this:

enter image description here

But it adds a bunch of unnecessary columns. As an alternative I thought I could add a filter to the top visual that would filter to "redcap_event_name"=="protocol_information" which would only be those top rows.... but given the visuals are linked, if I do that it removes everything from the bottom visual. I'd like to keep the link between the visuals so that if I select "study2" in the top visual, it'll highlight relevant study 2 information in the bottom one.

So my question is: what's the best approach for making the visuals I want? Are there special settings for visuals? Do I need to do something to the data first in the query? How should I go about this?

Solution

  • You might want to rework you data structure. At first glance, your flat source table could be parsed into two tables :

    • Protocol
    • Survey

    This can be done in PowerQuery.

    For Protocol :

    1. Select columns A to R.
    2. Filter on redcap_event (?) starts by "protocol_info"
    3. Delete empty rows

    For Survey

    1. Select columns A (to keep the protocol ID and be able to link both tables), T and U.
    2. Filter on redcap_event (?) starts by "survey"
    3. Delete empty rows.

    You should end up with the two table with a one-to-many relationship between Protocol[Protocol_ID] (column A) and Survey[Protocol_ID] (same)

    And it should make everything much easier: visuals, calculations...