Count of Records Within One Column

[JacobSzp]

Hi all,

Looking to solve the below issue within Power Query, not so much with dax.

Hopefully the below illustrates the issue clearly.

Thanks,

Jacob.

[Luis98 · Accepted solution]

Hi @JacobSzp 

You can duplicate this table, then group the column of colour by count of name, and then merge with the other table by colour column

[BA_Pete · Accepted solution]

Hi Jacob,

Select your [Fave Colour] column and go to Home tab > Group By.

In the dialog, keep the default count aggregated column, and also add another one using the 'All Rows' aggregator.

Expand the nested table column back out again.

Example Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1MzlbSUQpKTVGK1YlW8krMTkXiBpekloH4kak5OfnlYCGfzCpUAd/EomxUkYCMzBxUkeDEosQMNG35QL5TTmmqUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Fave Colour" = _t]),
    groupRows = Table.Group(Source, {"Fave Colour"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"allRows", each _, type table [Name=nullable text, Fave Colour=nullable text]}}),
    expansAllRows = Table.ExpandTableColumn(groupRows, "allRows", {"Name"}, {"Name"})
in
    expansAllRows

Example Output:

Pete

[v-jingzhang]

Hi @JacobSzp 

Have you tried @Luis98 's solution? It works well!

In addition, here is another solution if you want to add a custom column directly without duplicating a query. 

let favColor = [Fave Colour] in List.Count(List.Select(#"previous step"[Fave Colour], each _ = favColor))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Luis98 · Accepted solution]

Hi @JacobSzp 

You can duplicate this table, then group the column of colour by count of name, and then merge with the other table by colour column