Forum Discussion

SB_24's avatar
SB_24
Regular Visitor
3 years ago
Solved

Date Categories

Hi, 

 

I have the data below and the column headers include a date in them.  They have the relevant header and period . i.e Costs 2019 q1, Costs 2019 Q2. What I want is to break this down into the relevant year and Quarter so i can represent this through time series data. 

This is the data 

What I want is to wrangle the data such that I can have a line graph/ Clustered bar graph comparing the Sales and Cost of Sales with the Year and Quarter on the X axis and the Sales amount on the Y.

Anyone ever encountered this?

Interesting Links
Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    SB_24 

    1. select the first two column and unpivot other columns

     

    2. Replace sales with Sales/ in Attribute column

     

    3. split column by delimiter "/"

     

    pls see the attachment below

2 Replies

  • johnyip's avatar
    johnyip
    Solution Sage
    3 years ago

    SB_24 , never used of this function, but I think you can use Transpose in PowerQuery.

     

     

    and then you may need to add another two columns to determine if the records are sales / cost of sales, and the associated year.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    SB_24 

    1. select the first two column and unpivot other columns

     

    2. Replace sales with Sales/ in Attribute column

     

    3. split column by delimiter "/"

     

    pls see the attachment below