locked
How to combine sets of pivoted data with uneven no. of columns in each set within the same worksheet? RRS feed

  • Question

  • Hello everyone,

    Each month I get an Excel with pivoted data with dates on the top columns from left to right and recipes and unit rates in rows on the left hand side, and recipe quantities mentioned under each date.

    The trouble is the data is in groups (each data set pertains to a particular unit, hence multiple units will have data sets pertaining to that unit). 

    Some units have consumption only on specific days, hence date columns are uneven in comparison with other data sets.

    For example, if data set # 1 has consumption quantities on all 30 days, data set # 2 may have consumption only on 1 day, say 18th of the month (30 date columns vs 1 column1). 

    All such data sets are stored in a single worksheet.

    Is there a trick in PQ to cleanse this kind of data, to arrive at a clean data set, even linear format will be fine, as that can be ultimately pivoted.

    Thanks for taking out the time to read this question.

    Best regards,

    Darshan

    Friday, December 27, 2019 2:02 PM

Answers

  • This is very easy in PQ. Select the columns that are not pivoted date columns and are the same every month (you can multi-select columns in PQ if necessary by holding the Ctrl key) then right click and select the "Unpivot Other Columns" option. This will put all your dates in one column and the corresponding values in another so it is easy to repivot the data later. 
    Monday, December 30, 2019 10:14 PM