Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.