Count number of times value appears based on another column

[ryan_b_fiting]

Hello PQ Community - 

I am looking for some help.  I have a data table that contains a wireless number, a billing date and a partial/full flag.  What I am looking to do is count the number of times that the partial flag = "Partial" for a given wireless number.  Here is a basic sample of the data:

Wireless Number	Billing Date	Partial/Full
555-555-5555	1/1/2023	Partial
555-555-5555	2/1/2023	Full
555-555-5555	3/1/2023	Full
555-555-5555	4/1/2023	Full
555-555-5555	5/1/2023	Full
555-555-5555	6/1/2023	Full
999-999-9999	2/1/2023	Full
999-999-9999	3/1/2023	Full
999-999-9999	4/1/2023	Full
999-999-9999	5/1/2023	Full
999-999-9999	6/1/2023	Full
111-111-1111	1/1/2023	Partial
111-111-1111	2/1/2023	Full
111-111-1111	3/1/2023	Full
111-111-1111	4/1/2023	Full
111-111-1111	5/1/2023	Full
111-111-1111	6/1/2023	Partial

I need the output to look like this at the end in Power Query:

Wireless Number	Billing Date	Partial/Full	Partial Count
555-555-5555	1/1/2023	Partial	1
555-555-5555	2/1/2023	Full	1
555-555-5555	3/1/2023	Full	1
555-555-5555	4/1/2023	Full	1
555-555-5555	5/1/2023	Full	1
555-555-5555	6/1/2023	Full	1
999-999-9999	2/1/2023	Full	0
999-999-9999	3/1/2023	Full	0
999-999-9999	4/1/2023	Full	0
999-999-9999	5/1/2023	Full	0
999-999-9999	6/1/2023	Full	0
111-111-1111	1/1/2023	Partial	2
111-111-1111	2/1/2023	Full	2
111-111-1111	3/1/2023	Full	2
111-111-1111	4/1/2023	Full	2
111-111-1111	5/1/2023	Full	2
111-111-1111	6/1/2023	Partial	2

I am having a hard time figuring out how to handle this in Power Query, so it will count the Partial flag by wireless number and apply that count to all rows for the number.

Any quick help would be greatly appreciated!

Thanks
Ryan F.

[adudani · Accepted solution]

hi @ryan_b_fiting 

Create a blank query , copy and paste the below code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdGxCoAgFIXhd3FO7Go38AWa28XBMXCKev8ahOB0xOGCwzf8B1MyqmrbqZmMOHF+9uF97uW8jlJNnn7Kf2q7KyVhTJYx0TFZCYkx2naxkwuE5QJhuUBYLhCWKyK2nfT/ABQbBYSNAsJGAWGjgKykOD8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Wireless Number" = _t, #"Billing Date" = _t, #"Partial/Full" = _t]),
    #"Partial Count" = Table.AddColumn(Source, "Partial Count", each if [#"Partial/Full"] = "Partial" then 1 else 0),
    #"Changed Type" = Table.TransformColumnTypes(#"Partial Count",{{"Wireless Number", type text}, {"Billing Date", type date}, {"Partial/Full", type text}, {"Partial Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Wireless Number"}, {{"Detail", each _, type table [Wireless Number=nullable text, Billing Date=nullable date, #"Partial/Full"=nullable text, Partial Count=nullable number]}, {"PC", each List.Sum([Partial Count]), type nullable number}}),
    #"Expanded Detail" = Table.ExpandTableColumn(#"Grouped Rows", "Detail", {"Wireless Number", "Billing Date", "Partial/Full", "Partial Count"}, {"Wireless Number.1", "Billing Date", "Partial/Full", "Partial Count"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Detail",{"Partial Count", "Wireless Number"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Wireless Number.1", "Wireless Number"}, {"PC", "Partial Count"}})
in
    #"Renamed Columns"

Output:

Steps:

1. created a partial count column, if partial then 1 else 0

2. grouped by wireless number, sum the partial count column, another aggregation with operation: all rows

3. expand the table.

the code could be made more dynamic/ shorter.

let me know if this works for you.

[ryan_b_fiting]

@adudani thank you.  This worked perfectly for my need.  I was able to implement the Table.Group into my full data set and add the the additional columns that I needed in the detail and expand.