reducing number of columns?

[bryanc78]

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?

[Pragati11]

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

[bryanc78]

@Pragati11 

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

[Pragati11]

Hi @bryanc78 ,

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

Thanks,

Pragati

[bryanc78]

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

[bryanc78]

@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

[Ashish_Mathur]

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.

[bryanc78]

@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

[Ashish_Mathur]

Hi,

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

[bryanc78]

@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

[Ashish_Mathur]

Hi,

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

[bryanc78]

@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

[Ashish_Mathur]

Hi,

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

[bryanc78]

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

[Ashish_Mathur]

Hi,

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

=distinctcount(Data[TestID])

Hope this helps.

[Ashish_Mathur]

Hi,

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