Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I'd like to have a frequency count for each value in one column.
For example, the input is:
Name
A
A
A
B
B
C
C
Expected output:
Name Freq
A 3
A 3
A 3
B 2
B 2
C 2
C 2
I can get frequ by GROUP, but I simply want add a new column without GROUP them, I'd like to keep the all rows. Any help is appreciated!
Solved! Go to Solution.
Hi @tuoba ,
You can do it in two different ways
Power Query:
See code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZxgZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Distinct(#"Changed Type"[Name])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Count", each Table.RowCount(_), type number}, {"Count_Groups", each _, type table [Name=text]}}), #"Expanded Count_Groups" = Table.ExpandTableColumn(#"Grouped Rows", "Count_Groups", {"Name"}, {"Count_Groups.Name"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Count_Groups",{"Count_Groups.Name"}) in #"Removed Columns1"
DAX:
Add a column using the following code:
Count in DAX = CALCULATE(COUNT(Table1[Name]);ALLEXCEPT(Table1;Table1[Name]))
Check PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @tuoba ,
You can do it in two different ways
Power Query:
See code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIZxgZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Distinct(#"Changed Type"[Name])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Count", each Table.RowCount(_), type number}, {"Count_Groups", each _, type table [Name=text]}}), #"Expanded Count_Groups" = Table.ExpandTableColumn(#"Grouped Rows", "Count_Groups", {"Name"}, {"Count_Groups.Name"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Count_Groups",{"Count_Groups.Name"}) in #"Removed Columns1"
DAX:
Add a column using the following code:
Count in DAX = CALCULATE(COUNT(Table1[Name]);ALLEXCEPT(Table1;Table1[Name]))
Check PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you! Expand table works perfect! I have the Freq column added now, when I was trying to sort the Name by "Count" column, I went to the ribbon on the top, Modeling / Sor by Column, and choose the newly added column. But that sort the Name column asc, can I change the order of sorting there?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
59 | |
51 |
User | Count |
---|---|
165 | |
83 | |
68 | |
68 | |
59 |