Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

transpose

Hello,

 

I want to know how I can transpose this data from columns to rows. I have lot of columns like that but just for example sharing an example,

 

NamePlanindexProject NameScheduled DateL21L213L19N19L7L8L6N6L25 C1L25 C2N25 C1N25 C2
A02046BExisting1EU10/28/2022131112121 611    
A02046BExisting2EU10/28/2022232122222 622    
A02046BExisting3EU10/28/2022333132323 633    
A02092BNew1COV11/16/20221 112121 611131141301 
A02092BNew2COV11/16/20222 122222 622132142302 
A02092BNew3COV11/16/20223 132323 633133143303 
B01062AExisting1EU11/30/20221 112121 611131 301311
B01062AExisting2EU11/30/20222 122222 622132 302312
B01062AExisting3EU11/30/20223 132323 633133 303313
B02614AExisting1AN12/28/20221 1121  611131141301311
B02614AExisting2AN12/28/20222 1222  622132142302312
B02614AExisting3AN12/28/20223 1323  633133143303313

 

output:

band is the data that is been transposed from L21 to N25 C2 and tech is the column that is telling which band as there are duplicates.

 

NameBandScheduled DateProject NamePlanTech
A02046B110/28/2022EUExistingL21
A02046B210/28/2022EUExistingL21
A02046B310/28/2022EUExistingL21
A02046B3110/28/2022EUExistingL213
A02046B3210/28/2022EUExistingL213
A02046B3310/28/2022EUExistingL213
A02046B1110/28/2022EUExistingL19
A02046B1210/28/2022EUExistingL19
A02046B1310/28/2022EUExistingL19
A02046B2110/28/2022EUExistingN19
A02046B2210/28/2022EUExistingN19
A02046B2310/28/2022EUExistingN19
A02046B2110/28/2022EUExistingL7
A02046B2210/28/2022EUExistingL7
A02046B2310/28/2022EUExistingL7
A02046B6110/28/2022EUExistingL6
A02046B6210/28/2022EUExistingL6
A02046B6310/28/2022EUExistingL6
A02046B110/28/2022EUExistingN6
A02046B210/28/2022EUExistingN6
A02046B310/28/2022EUExistingN6

 

 

Need Help

5 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    HI Anonymous 

     

    Please go in Power Query and select the first five columns of your dataset using STRG so that all of them are selected.

     

    Then use the "unpivot other columns" feature.

     

     

    You can find more information under:

    Unpivot columns - Power Query | Microsoft Learn

     

    Best regards

    Michael

    -----------------------------------------------------

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Mike,

       

      these columns are derived from other columns so they are not in power querry editor. Can something be done in outside of power query editor?

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi Anonymous.


    What do you mean with derived from other columns? Do you work with Power BI Desktop? How did you et the data into Power BI Desktop?

     

    Best regards

    Michael

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      so in power querry those columns are not there, these are derived from combination of other columns that are there in the pbi

      • Mikelytics's avatar
        Mikelytics
        Resident Rockstar
        3 years ago

        Hi Anonymous 

         

        Sorry but I do not really understand.

         

         

        Do you even use Power BI Desktop?

        if yes, are the data you provided now in Power BI Desktop? 

        If yes, how did you get the data in Power BI Desktop?

         

        if no, do you have other data in Power BI Desktop and you want to add the data above?

        if yes, where are these data stored?

         

        A little bit more context would be helpful. 🙂

         

        Best regards

        Michael