Home Forums Power Pivot Power Query – Remove Duplicate Dates

Tagged: ,

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 2 years, 7 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #4319

    jon999
    Participant
    • Started: 11
    • Replies: 24
    • Total: 35

    Hi

    I have a table which has the months across the table for a calendar year (31/07/2016 to 30/06/2017). I have the same dates multiple times therefore the table has added a number to the end of the date eg 30/09/20162. How can I unpivot the dates for each year so that I can have a column for each year.

    I have attached a file to show the table and what I need to get it to look like.

    Thanks

     

    Attachments:
    You must be logged in to view attached files.
    #4333

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Hi Jon,

    This one I ventured into as if it were a riddle: had to stop and think a couple of times about where the data needed to end up.

    In PQ, after selecting just the Name column, there was an especially helpful starter transformation option: Unpivot -> Unpivot Other Columns.

    Everything then fell into place: split, replace, pivot, done.

    Interesting to me was that Power Query, immediately upon splitting, figured out by itself that one of the new columns would be a date. Cool!

    The attached workbook is in Excel 2016 format, but since it uses Power Query without Power Pivot, I think any modern version of Excel with PQ can access the Query Editor. If not, let me know and I will attach the M code in the next comment.

    Tom

    Attachments:
    You must be logged in to view attached files.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.