The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Dear all
I am trying to group some rows of my data based on the first two columns, Index and Account.Name, where the value on another columns is null. The result I want is to avoid null values on the last 2 columns, is it possible to be done in power query?
Solved! Go to Solution.
You can do this using Group By in the Power Query interface.
You just have to choose an arbitrary aggregation for the Customer Care Manager & Customer Support Coordinator columns (in case there are multiple values for a given Index/Account.Name combination, which I imagine shouldn't happen).
In my example I used Min, which automatically ignores nulls using the List.Min function.
Here is my M code, including creating the table. The relevant step is #"Grouped Rows".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUXIuLS7Jz00tUjAEcZx9wbRSrA4WWRA/2BnIBMkaIssaQfUawfRiyEL1GoFljZBljRGyxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Account.Name = _t, #"Customer Care Manager" = _t, #"Customer Support Coordinator" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Account.Name", type text}, {"Customer Care Manager", type text}, {"Customer Support Coordinator", type text}}),
OriginalTable = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Customer Care Manager", "Customer Support Coordinator"}),
#"Grouped Rows" = Table.Group(OriginalTable, {"Index", "Account.Name"}, {{"Customer Care Manager", each List.Min([Customer Care Manager]), type nullable text}, {"Customer Support Coordinator", each List.Min([Customer Support Coordinator]), type nullable text}})
in
#"Grouped Rows"
Regards,
Owen
You can do this using Group By in the Power Query interface.
You just have to choose an arbitrary aggregation for the Customer Care Manager & Customer Support Coordinator columns (in case there are multiple values for a given Index/Account.Name combination, which I imagine shouldn't happen).
In my example I used Min, which automatically ignores nulls using the List.Min function.
Here is my M code, including creating the table. The relevant step is #"Grouped Rows".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUXIuLS7Jz00tUjAEcZx9wbRSrA4WWRA/2BnIBMkaIssaQfUawfRiyEL1GoFljZBljRGyxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Account.Name = _t, #"Customer Care Manager" = _t, #"Customer Support Coordinator" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Account.Name", type text}, {"Customer Care Manager", type text}, {"Customer Support Coordinator", type text}}),
OriginalTable = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Customer Care Manager", "Customer Support Coordinator"}),
#"Grouped Rows" = Table.Group(OriginalTable, {"Index", "Account.Name"}, {{"Customer Care Manager", each List.Min([Customer Care Manager]), type nullable text}, {"Customer Support Coordinator", each List.Min([Customer Support Coordinator]), type nullable text}})
in
#"Grouped Rows"
Regards,
Owen
User | Count |
---|---|
83 | |
83 | |
37 | |
34 | |
32 |
User | Count |
---|---|
92 | |
79 | |
62 | |
53 | |
51 |