Forum Discussion
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 filtering, so one slicer would be company, another slicer would be provider (with all column names I mentioned) so when we select the company name from list, I have to get each company name vertically showing it's respective provider name and their value as shown below.
I'm confused how would I take this forward. please help!
I just created a slicer with list of provider names using field parameters, but not sure about the next steps? The provider column should be used as a filter to sort the company name and also as a category on the tree map visualisation.
Right now, my table is showing like this when I select the company name from the filter
| Company | AB | CD | EF | GH | IJ |
| XYZ Ltd | 123 | 456 | 789 | 158 | 345 |
But I want the table to be shown like the below, company name, provider, values vertically.
| Company | Provider | Values |
| XYZ Ltd | AB | 123 |
| XYZ Ltd | CD | 456 |
| XYZ Ltd | EF | 789 |
| XYZ Ltd | GH | 158 |
| XYZ Ltd | IJ | 345 |
You can do table transformation on PowerQuery. Particularly the "Unpivot". Here is from the sample you have given.
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.
10 Replies
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.
Hi TejuCH_99
I am sure you know unpivot function in Power Query which is going to produce your required output since your title says "unpivot".
I attach pbix file.
Hi, thanks for your response. I know Unpivot does that, but I don't have company name in the same table in my dataset to select for unpivot, so company name is from another table, which will be used in a slicer to sort. I just have provider names as columns which should be listed in an another slicer to filter. So, for example my below table is shown with company names and providers, but I want them to be shown vertically as said above
My data set . Also, I have few more additional columns in provider table, but want to create a slicer with just provider columns AB, CD, EF, GH, IJ.
So, I want to create 2 things now, one is a slicer using the provider columns, and then in below table when we select the company name, it should be shown vertically. Hope I'm clear this time. Thanks for your help!
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,
Hi,
So I used ID column to build the relationship b/w both the tables and pull data from provider table
Unpivot from that ID
You can do table transformation on PowerQuery. Particularly the "Unpivot". Here is from the sample you have given.
