Unpivot/Pivot

[TejuCH_99]

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

[v-nuoc-msft · Accepted solution]

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.

[AngeloParana]

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

[DataNinja777]

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]

Hi, 

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

[AngeloParana]

Unpivot from that ID

[DataNinja777]

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]

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]

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

[DataNinja777]

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. 

[TejuCH_99]

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!