Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Shift row contents up to match adjacent column values

  • 1.  Shift row contents up to match adjacent column values

    Posted 9 days ago
    Hello all,

    I am working with what are essentially survey data questions that were all in one column. I pivoted that column to the column that has the information from the person filling out the survey. 

    This of course creates multiple columns and multiple rows per survey response. I then need to essentially merge the rows into a single record per survey response (which I would do by unpivoting then pivoting again attribute to value as explained here https://community.powerbi.com/t5/Desktop/Combining-rows-based-on-unique-id-and-combining-information/td-p/29466 ). Furthermore, some of the answer questions are numerical and others are categorical and one category is multi-select - so I have to have to split the column and unpivot to get a record per survey response per variation.

    All that to be said, I have made it very close and all I need to do is shift row contents up to match the adjacent column! See below, any ideas?

    image.png

    My goal is to achieve one column for the type of response and one column for the count.

    Sincerely,
    Trent Brewer


  • 2.  RE: Shift row contents up to match adjacent column values

    Posted 8 days ago
    Why don't you right click on the column Merged.2, then Fill, UP.
    Then click on Merged.1, and  click on the drop down arrow, and unselect null.. this will remove all the unwanted rows from your dataset.

    ------------------------------
    Ka Ly
    Clinical Informatics Program Manager
    ------------------------------



  • 3.  RE: Shift row contents up to match adjacent column values

    Posted 8 days ago
    I actually did that exactly for some other categorical columns to the left. However, since the pattern is irregular and there may to clusters of two or more in the merged.1 column I can't do a fill-up and trust that the data will be correct. For example, The first time there is a grouping of two in the merged.1 column, the values that need to be next to those are 1 and 11. If I fill-up, it would put the values as 1 and 1.

    Trent

    ------------------------------
    Trent Brewer
    Manager of External Affairs
    Edmond OK
    ------------------------------