Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tooba
Frequent Visitor

convert columns to rows for a bar chart

Hi everyone,

 

I have a data set in the following format:

 

Main Equipment

Sub Equipment

Sub-Sub Equipment

E1

S1

SS1

E1

S1

SS1

E2

S2

SS2

E2

S3

SS4

 

I want to convert it to the following format and make a bar chart for it.

Equipment Level

Count

Main Equipment

2

Sub Equipment

3

Sub-Sub Equipment

3

 

If I use matrix and transpose the rows and then, use count(distinct), I can make the following plot. The issue is that it doesn't show the name of equipment level on X-xis:

 

Bar chart based on matrixBar chart based on matrix

 

 

 

 

 

 

 

 

 

 

Following imaginary output is what I look for:

 

Desired barchartDesired barchart

 

Please consider that there are other visuals build based on this data set and I can't change the format of the data set in the Power Query.

 

 

Thanks.

 

 

 

1 ACCEPTED SOLUTION
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @tooba ,

You can just duplicate your data source and transform the data like how @HashamNiaz  said.

 

Then use this new dataset for your visual.

As you are dupicating the dataset, you will have it dynamic as well as you don't have to hard code.

As the dataset is the duplicte of the original dataset, once the orginal dataset updates, this too gets refreshed with the latest data

 

Hope this helps!!

View solution in original post

5 REPLIES 5
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @tooba ,

You can just duplicate your data source and transform the data like how @HashamNiaz  said.

 

Then use this new dataset for your visual.

As you are dupicating the dataset, you will have it dynamic as well as you don't have to hard code.

As the dataset is the duplicte of the original dataset, once the orginal dataset updates, this too gets refreshed with the latest data

 

Hope this helps!!

tooba
Frequent Visitor

Hi @Jihwan_Kim 

I can't do it. It's a sort of hard coding:)

We can't create one table per each request as these tables will be added to our model and it's not a best practice to do so. More importantly, I want to use this visualisation as the first level of a drill-down scenario, so it should be related to other dimensions which I have. 

 

 

HashamNiaz
Solution Sage
Solution Sage

Hi @tooba !

You can simply go to Power Query editor (Transform Data), select the all 3 column and perform Unpivot Transformation.

 

UnpivotUnpivotPivotPivot

 

Relabel the column name, now in your report simply plot the chart against your category & distinct count.

 

ChartChart

 

Regards,

Hasham

@HashamNiaz 

Thanks for your reply but I can't reshape the data set format for this request. There are other visuals using this data set in the original format.

Jihwan_Kim
Super User
Super User

Hi, @tooba 

I assume you can manually create one more axis table inside Power BI like below.

Please check the below picture and the sample pbix file's link down below.

all measures are in the sample pbix file.

In this case, I used explicit measures, not implicit measures.

 

Picture1.png

 

https://www.dropbox.com/s/3nzqvl9ggjagz96/tooba.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.