Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count of Records Within One Column

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

     

    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's avatar
    BA_Pete
    3 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

     

    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

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    Have you tried Anonymous '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.

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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