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 ,

 

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?

 

  • AngeloParana's avatar
    AngeloParana
    Resolver I
    2 years ago

    Before unpivoting,select all fields that you dont want to include.. Including the ID