Forum Discussion

Yuiitsu's avatar
Yuiitsu
Icon for Helper V rankHelper V
3 years ago
Solved

Unpivot multiple headers in a data (header has merged cells)

Hi Experts, 

 

I am trying to unpivot this set of data that has 2 headers. (my 1st row's headers has merged cells.)

When I inport to power query it looks like this.

 

I've tried transpose and unpivot but it doesnt seems to work. Looked around other post but cannot find a solution for my case.

Please help if you have the solution for me.

Need Help
Tips and Tricks
Tutorial Requests
  • Yuiitsu's avatar
    Yuiitsu
    3 years ago

    Thank you.

     

    I changed my "as of" to dates, created a calendar table and the linked them up in a relationship.

    Created a month column in my date table and result is as follow:

     

4 Replies

  • visheshjain's avatar
    visheshjain
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Hi Yuiitsu,

     

    Please can you share some sample data and your required output.

    Tranpose and fill down should have worked.

    Its one of the things taught in the MS PBI tutorial videos and your problem does look similar.

     

    Thank you,

    Vishesh Jain

    • Yuiitsu's avatar
      Yuiitsu
      Icon for Helper V rankHelper V
      3 years ago

      HI!

       

      I actually managed to work around with transpose and fill down and get the below result:

       

       

      But i now face another issue, I want it to show from As of Oct, As of Nov, As of Dec instead of by alphabetical order. Can you assist me with that?

       

         Oct-22Nov-22Dec-22    Jan-23 Feb-23 Mar-23
      Type CustomerProductActualActualAs of OctAs of NovAs of DecAs of NovAs of DecAs of NovAs of DecAs of Dec
      BOHGX3All           
      OthersIME3DI+CT     18,000,000   18,000,000
      OthersMSECT          
      OthersMSESD          
      BOHMSESPS9,704,100         
      BOHUMCAll     15,000,000     
      BOESDSMTS135,411,155         
      BOHMSECT/SD4,224,726         
      BOEAMFCT          
      OthersSOTPS   4,000,000  4,000,000   
      OthersUMCTPS   21,000,00021,000,000     
      EOSIntend 5TS          
      OthersTFTS 3,200,000        
      BOH360ES/P5          
      OthersTerraCT  4,759,2004,759,2004,759,200     
      OthersAMFCT  8,000,0001,500,0001,500,000     
      BOEUMCCT   3,237,6753,237,675     
      BOEUMCCT   7,329,6057,329,605     
      BOESTMTPS  6,000,000       
      BOHGX3PVD  5,000,000       
      OthersUMC CT   31,500,000  31,500,000   
      OthersUMC SPS   15,000,000  15,000,000   
      BOHLumCT 2,939,720 2,500,000      
      BOHGX3All        28,000,00028,000,000 
      BOHIntendTS    1,414,560     
      BOEUMCCT      11,000,000   
      • visheshjain's avatar
        visheshjain
        Icon for Impactful Individual rankImpactful Individual
        3 years ago

        Hi Yuiitsu,

         

        Ideally you should be using a calendar dimesnion table for it, create realtionship between the 2 tables after converting your months into the first date of that month in your fact table.

         

        Your calendar table should have the MMM-YY column, which is sorted by the Year Month Key column and use that in your visuals.

        So in your calendar table,  Oct-22 will have a corresponding numeric value 202210.

         

        Hope this solves your issue.

         

        Thank you,

        Vishesh Jain