Forum Discussion

Kees's avatar
Kees
Icon for Helper I rankHelper I
3 years ago
Solved

Pivoting from column to row

Hi 

 

I'm kind of and Power BI new bee and not sure if what I want is (for me) possible to achive. I've checked the forum and possibly found some simular question thought I could not make a loaf of bread of it. So I'll just give it a shot and see if someone can help me to see the light.

 

Source is Table 1 hold project tasks and in the columns the hours spend.

 

Target Table 2

if of hours are filled in table 1 a row is created with the project, the task, the month and the hours.  

 

Hope someone could help me with this hurdle.

 

Brgds Kees

Need Help
Show and Tell
  • Migasuke's avatar
    Migasuke
    3 years ago

    Hi Kees ,

    This can be achieved quite well.
    Just use unpivoting in Power Query.

    1. Select columns with hours:

    2. Go to transform pane and click on unpivot:

    3. This will be your outcome:


    Ofcourse you just rename your columns accordingly.

     

5 Replies

  • Migasuke's avatar
    Migasuke
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi Kees ,

    This can be achieved quite well.
    Just use unpivoting in Power Query.

    1. Select columns with hours:

    2. Go to transform pane and click on unpivot:

    3. This will be your outcome:


    Ofcourse you just rename your columns accordingly.

     

    • Kees's avatar
      Kees
      Icon for Helper I rankHelper I
      3 years ago

      Hi Migasuke

       

      Thanks for the quick response!

       

      I think I understand what it does and actually it is close to what I need. 

      The table 1 has a lot more info & data that I would like to keep as it is.

      When I followed the steps you described it kind of messes up table 1, at least it looks like that.

      Perhaps I need to have a sychonized and separate table a selection of columns in it, and then do the unpovit. Will that then give me a new/separate unpivoted target table. that I can use? . .... again not sure if that is possible,,,


      Hope I have described the situation a bit more clear what I'm looking for.

       

      Again thanks for you response although not hey what I need, very usefull for the future!

       

      Brgds Kees

       

      • Migasuke's avatar
        Migasuke
        Icon for Memorable Member rankMemorable Member
        3 years ago

        In case you need to keep information from more columns, which are not mentioned above, you should still be fine if you just unpivot numeric values (hours).

        I am not sure what you mean by this?

        Perhaps I need to have a sychonized and separate table a selection of columns in it, and then do the unpovit. Will that then give me a new/separate unpivoted target table. that I can use? . .... again not sure if that is possible,,



        If you can, please provide some screenshot or dummy data, might be very helpful.

         

  • PBIdashboards's avatar
    PBIdashboards
    Icon for Post Patron rankPost Patron
    2 months ago

    The Power Query unpivot solution (select columns → Transform → Unpivot Other Columns) is the right fix for reshaping the data structure itself.

    Worth flagging a related scenario for anyone landing here: if the goal isn't reshaping the underlying data but rather letting end-users switch between row and column layout themselves inside a published Power BI report  that's a different problem. Power Query transforms are fixed at the model level; users in Power BI Service can't pivot/unpivot on the fly.

    For that use case, Flexa Tables (AppSource, Microsoft-certified) lets users drag fields between rows and columns directly in the published report  no Power Query changes, no Desktop access needed. Useful when Finance or business users keep requesting different cuts of the same data.

    Free trial: https://appsource.microsoft.com/en-us/product/power-bi-visuals/devspearllc1670524393721.flexa_tables