cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Concat Member
Member

Creating new table with counts of distinct values; help

Hello all,

 

I have a table with 30 000 rows.  One column in this row has a string value.  There are only 40 some distinct values that can be in this column.

I want to create a new table, which lists the distinct values in one column, and then in the second column, a count of how many times that value appears.  I am very new to PowerBI, and have looked at perhaps a dozen tutorials and forum posts with no solution...

 

So far, I have created the new table trying to show the distinct values in the first column:

 

Table = DISTINCT(Sheet1[StringValue])

 

My latest attempt to fill the 2nd column with the corresponding counts failed when I tried this:

 

Column = calculate(countrows(sheet1),allexcept(Sheet1,Sheet1[StringValue]))

 

Also, I must be missing something fundamental about Tables.  My table is blank until I add that 2nd column.  I assumed that Table = DISTINCT(Sheet1[StringValue]) would return at least one column with data...  but it doesn't.  Only after I add a second column will any data be present.

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
samdthompson Established Member
Established Member

Re: Creating new table with counts of distinct values; help

ok so something like =SUMMARIZE(FILTER(Table1,Table1[StingValue]<>blank()),Table1[StingValue]) should work

7 REPLIES 7
samdthompson Established Member
Established Member

Re: Creating new table with counts of distinct values; help

Hi welcome to our community. Try making a new table:

 

1. on ribbon click modelling

2. click New Table

3. enter formula =SUMMARIZE(Sheet1[StringValue])

4. Create relationship between the tables based on the string

5. Create second column on the new table =Calculate(counta(stringValue),filter(Sheet1))

 

This should give you the distinct list. Perhaps the better solution would be to make a table or matrix in powerbi. it will give the you ability to show a count pretty easily.

Concat Member
Member

Re: Creating new table with counts of distinct values; help

Thank you for the welcome and for the reply.

 

Unfortunately, I can't create a relationship because there is no column in the original speadsheet with all unique values.  I suppose I could add a column to that in order to create one with unique values...  but is this necessary?

 

I tried to create the 2nd column anyway usuing your calculate() formula, but it appears that it needs a 2nd argument for filter().

 

It's all a bit mind numbing since if I throw the column [stringvalue] into a pie chart it shows me the counts for each value without a problem!  The trick seems to be getting it in a tabular form...

samdthompson Established Member
Established Member

Re: Creating new table with counts of distinct values; help

You can create a relationship as you are creating a unique table of values. Its the =summarize() that will create your unique ID.

 

The format for the argument containing the filter will be =Calculate(counta(sheet1[stringValue]),filter(Sheet1)=table[StringValue])

Concat Member
Member

Re: Creating new table with counts of distinct values; help

Ah right, but I have a null/blank value generated from summarize, so I can't use that as my unique ID.  Is there a way to filter out blank values from the summarize function?

samdthompson Established Member
Established Member

Re: Creating new table with counts of distinct values; help

ok so something like =SUMMARIZE(FILTER(Table1,Table1[StingValue]<>blank()),Table1[StingValue]) should work

Concat Member
Member

Re: Creating new table with counts of distinct values; help

Perfect.  Thank you.

I also, managed to get the counts in the 2nd column using CALCULATE(Counta(Sheet1[StringValue]),ALLEXCEPT('Table','Table'[StringValue]))

Kobur Occasional Visitor
Occasional Visitor

Re: Creating new table with counts of distinct values; help

The following worked for me;

example. Say you have a table Q3, with a column Manufacturer which repeats and you want to create a table called Manufacturers with only Distinct Manufactures and the occurrence of each

Go to "new table" under "modeling" tab

Use the expression below

 

Manufacturers = SUMMARIZE(Q3, Q3[Manufacturer], "Totals", COUNT(Q3[Manufacturer]))

 

This will create a table as below

 

 

Manufacturers           Totals

Daimler                        256

BMW                            200