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
Solved! Go to Solution.
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.
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...
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])
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?
Perfect. Thank you.
I also, managed to get the counts in the 2nd column using CALCULATE(Counta(Sheet1[StringValue]),ALLEXCEPT('Table','Table'[StringValue]))
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