Count number of occurrence of value in a Custom column = Power Query

[PBIfolks]

Hi

Sample Item table with duplicate Item Codes. In the Output, I like to capture Item Code with date, if the there is no date, then also include like to get the Item Code. Incase of Duplicate Item code, capture Item which got E Date. As shown in the Output screenshot.

Sofar i managed to get the desired output as shown below.
   Count = List.Count(
   Table.SelectRows(
   Key,
   (C) => [Key] = C[Key]
    )[Key]
    ))

The Custom Column code is working, but when i filter the count this is taking ages to load the data. The data source is not very heavy it only got two excel tbl with 17K and 8K rows of data.

So i like to know is there a different technique or PQ code to achieve this in Power Query, pls?

Many Thanks

[v-jingzhang · Accepted solution]

Hi @PBIfolks 

You can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lFyBGIjAyMjfXN9CxjT0FDfyFApVidaydgUKOYEEzfRNwLxwTJmIJYzWMbQUt9Y38gIiW2GVYk5WG8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"Item Name" = _t, #"M Date" = _t, #"E Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Code", Int64.Type}, {"Item Name", type text}, {"M Date", type date}, {"E Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item Code"}, {{"All Data", each if Table.RowCount(_) > 1 then Table.SelectRows(_, each [E Date] <> null) else _, type table [Item Code=nullable number, Item Name=nullable text, M Date=nullable date, E Date=nullable date]}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"All Data"}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Removed Other Columns", "All Data", {"Item Code", "Item Name", "M Date", "E Date"}, {"Item Code", "Item Name", "M Date", "E Date"})
in
    #"Expanded All Data"

Steps are:

1. Group by Item Code and filter out null E Date rows for duplicated Item rows in the output table column.

2. Remove Item Code column. 

3. Expand the table column obtained from step 1. 

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

[PBIfolks]

Thanks @v-jingzhang @worked well.