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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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