Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
tuoba
Frequent Visitor

Add new column to show frequency(No Group)

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!

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @tuoba ,

 

You can do it in two different ways

 

Power Query:

  • Make Group by
  • Choose Advanced
  • Add two summarinzing rows:
    • Count
    • All

Group.png

  • Expand the column that as the "table" rows
  • Delete the addtional column you just created

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @tuoba ,

 

You can do it in two different ways

 

Power Query:

  • Make Group by
  • Choose Advanced
  • Add two summarinzing rows:
    • Count
    • All

Group.png

  • Expand the column that as the "table" rows
  • Delete the addtional column you just created

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



tuoba
Frequent Visitor

Thank 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? 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.