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.
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
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.
User | Count |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |