Forum Discussion

TejuCH_99's avatar
TejuCH_99
New Member
2 years ago
Solved

Unpivot/Pivot

Hi, In my dataset, I have a table with 7 different columns with provider names say AB, CD, EF etc for each company. I was tasked to create a dashbard with few slicers on top which can help in filteri...
Need Help
  • AngeloParana's avatar
    AngeloParana
    2 years ago

    You can do table transformation on PowerQuery. Particularly the "Unpivot". Here is from the sample you have given. 

     

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi TejuCH_99 

     

    AngeloParana DataNinja777 Thank you very much for your patience in responding! I think the options you offer make a lot of sense.

     

    TejuCH_99 If you still have questions, allow me to add something here.

     

    Make sure there are no relationships between your tables before you unpivot. Because of the existence of relationships, it is possible that your operation will fail.

     

    If you don't want other columns to affect your data, then please don't select these unwanted columns before you unpivot. For example, you can select several columns for unpivot as shown in the figure:

     

     

    You can get a table like this.

     

    Create a relationship.

     

    Create the slicer you need. For example, company and provider:

     

     

    I hope this helps.

     

    Regards,

    Nono Chen

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

     

DataNinja777's avatar
DataNinja777
Super User
2 years ago

Hi TejuCH_99 ,

 

Would you mind sharing your data model view?  My understanding is that both 'Company' table and 'Provider' table are dimension tables and there is a separate fact table which are linked to those dimension table, or is the 'Company' table a dimension table while 'Provider' table is a fact table with numerical value as you wrote above, but in this case, without the company code field in the 'Provider' table, how would you be able to link the values to both company dimension and provider dimension?  

Best regards,

  • TejuCH_99's avatar
    TejuCH_99
    New Member
    2 years ago

    Hi, 

    So I used ID column to build the relationship b/w both the tables and pull data from provider table

     

     

    • AngeloParana's avatar
      AngeloParana
      Resolver I
      2 years ago

      Unpivot from that ID

    • DataNinja777's avatar
      DataNinja777
      Super User
      2 years ago

      Hi TejuCH_99 ,

       

      Thanks for the clarification. In that case, you can unpivot the 'Provider' table as discussed before using power query "Unpivot" and then create one to many relationship between the 'Company' table and 'Provider' table to do the analysis you need to do.  The important thing is that in Power BI data model, you do not need to create one big wide table (combining fact table and dimension tables in one big frankentable), and it is recommended to keep them as separate tables in data model and instead create relationships for a flexible high performing data analysis.  

      Best regards,

      • TejuCH_99's avatar
        TejuCH_99
        New Member
        2 years ago

        Hi DataNinja777 and AngeloParana ,

        Thanks for your responses. I did unpivoted from ID column but there're other columns like Firm, Total etc which I don't want them to be shown in my output. I just want the values only for providers AB,CD,EF,GH,IJ. Any other way I can get rid of them please? I have many more additional columns other than provider names in my original dataset. Thanks again for your support. Also, how do I get those provider names onto slicer please?