cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bryanc78
Helper IV
Helper IV

reducing number of columns?

I have a datset with about 50 columns that I'm bringing into PBI

 

Table 1 - only uses about 5 of them

Table 2 - is a duplicate (reference) of Table 1 where I take about 30 of those columns and then unpivot them.  Again, on this table, I only use about 5 columns.

 

I know I can bring in the dataset twice and delete/unpivot as needed but that's 2 datasets I have to manage and refresh.


Any other options?

15 REPLIES 15
Pragati11
Super User
Super User

Hi @bryanc78 ,

 

Can't you just use your refresnced table for all the visualisations?

In that way you don't need the other table.

Basically do everything on a single table and use it for visualisations.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




@Pragati11 

 

Since I'm not unpivotting all the columns, wouldn't that cause my other fields to basically be duplicated 30 times?

Hi @bryanc78 ,

 

This should not create any issue. It will rather help with loading less tables in Power BI ans improve performance.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




If that's the case, then no need to create the 2nd reference table.  I'll just unpivot the initial table.  With the unpivotted columns being duplicated 30 times, I just assumed it would make any aggregating useless

 

I'll try it shortly and report back here my findings

@Pragati11 @Ashish_Mathur 

 

This did not work.  I should have only 500 items in my bar chart but after unpivoting the main table, I now have over 20k items

 

Is there a setting that I'm missing?

 

My dataset is basically a test with 30 questions going across 50 columns

 

name  date score question 1 question 2 question 3 etc

 

I'm unpittoing all the questions to make it easier to chart but now "score" is being duplicated 30 times

Capture.PNG

 

Hi,

So now in your visual, if you wish to know the score per name, then drag Namr to the visual and write this measure

=min(Data[Score])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

That did not help.  I want to report on the Score and not the name

 

So, if I had 90 Pass and 10 Fail, I want the bar chart to show 90 and 10

 

Right now, one test is being counted 30 times

Hi,

Share data in a format that can be pasted in MS Excel and also show your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

Here is the data that is in my csv file that PBI is connected to

 

 

Name 	Date	Score	Question 1	Question 2	Question 3	Question 4	Question 5	Question 6
Bob	7/1/2020	Pass	Red	Orange	Football	TV	Bourbon	Book

 

 

Here is how it looks when I unpivot all the "question" columns

 

Name 	Date	Score	Questions	Question Answers
Bob	7/1/2020	Pass	Question 1	Red
Bob	7/1/2020	Pass	Question 2	Orange
Bob	7/1/2020	Pass	Question 3	Football
Bob	7/1/2020	Pass	Question 4	TV
Bob	7/1/2020	Pass	Question 5	Bourbon
Bob	7/1/2020	Pass	Question 6	Book

 

 

If just unpivot the original table, every column that was not unpivoted gets duplicated 30 times so I cant get accurate counts on it.  What I want to happen is to only have the unpivoted columns count once and I'm not sure if that is possible or not

Hi,

What do you want to calculated.  Please be very clear.  Show the expected result clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

I want to be able to count the total number of tests that passed, the total number of tests that failed, the total number of tests that Bob took etc

 

If Bob took 1 test that contained 30 questions and he passed it, I want it to show that

  1. Bob took 1 test
  2. 1 test was taken
  3. 1 passed test was taken

I do not want it to show that:

  1. Bob took 30 tests
  2. 30 tests was taken
  3. 30 passed tests were taken

Please see below.  The chart on the left is correct.  The chart on the right is pointing to the same data fields but on the unpivoted columns.  As you can see, each test is being duplicated 30+ times

Capture.PNG

 

Hi,

Do you have a Test ID column in your base data?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur   yes, I use that field to connect the referenced table to the main table

Hi,

If you have a TestID, then after unpivoting, you may use this measure

=distinctcount(Data[TestID])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

Yes, that will.  But that should not cause problems.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.