Creating new table with counts of distinct values; help

[Anonymous]

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

[Kobur]

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

[samdthompson]

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.

[Anonymous]

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]

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])

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

Perfect.  Thank you.

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