Forum Discussion

JR_Lee's avatar
JR_Lee
Frequent Visitor
4 years ago
Solved

Power Query transformation help

I'm struggling to transform the following data:

 


so it looks something like the below for each salesperson and each month:



this is where i've got to but i cant seem to isolate the month names:


Attached xlsx link is here

 

Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    Hi JR_Lee ,
    1) add a column with the following syntax: "if [Column2] = "Coke[PG 29]" then [Column1] else null
    2) Check that new column and fill down values. This will returne a column with the repective months in rows.

     

7 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Hi JR_Lee ,
    1) add a column with the following syntax: "if [Column2] = "Coke[PG 29]" then [Column1] else null
    2) Check that new column and fill down values. This will returne a column with the repective months in rows.

     

    • JR_Lee's avatar
      JR_Lee
      Frequent Visitor
      4 years ago

      Hi ImkeF 

      Not sure if I have misundestood but when I add the column I get the following (which inclueds the salesperson name)

       

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Hi JR_Lee ,

    you must do that before the unpivoting.
    Either before or after the Promoted Headers step:


     

     

    • JR_Lee's avatar
      JR_Lee
      Frequent Visitor
      4 years ago

      amitchandakafter unpivoting all product columns how can i deal with the following:

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        JR_Lee , I think this requires a little bit of clean-up outside power bi, having a month on a column.

         

        But check with M expert.

        ImkeF , any suggestion for this