Forum Discussion
How to show the rows with more data in PowerQuery/PowerBI?
I'm struggling how to identify the rows that have more data based on a column, in this case: Owner, if there is no Owner at all, show the row with no owner. PowerQuery is preferable, but if that is not possible, using PowerBI
Example:
| Color | State | Owner |
| Blue | MI | |
| Blue | MI | Snoopy |
| Yellow | AZ | Charlie |
| Yellow | AZ | Charlie |
| Red | CO | |
| Red | CO |
Desired Result:
| Color | State | Owner |
| Blue | MI | Snoopy |
| Yellow | AZ | Charlie |
| Red | CO |
This is more steps. I would personally try to push this logic back to the source but it can be done in power query.
To make this work in power query I had to create another copy of the table and join the original table to the copy. I also had to make a key with a merge on Owner, State, and, Color in both the copy and the original I used that to join in the merge step. Then I pulled the toy from the joined table.you can use group by command to solve this.
use the next formula
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"State", type text}, {"Owner", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Color", "State"}, {{"Count", each Text.Combine(List.Distinct(([Owner])))}})
in
#"Grouped Rows"
result inHi heriberto_mb, check this:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfL1BBLJiUVAUilWB1W4pCgxMw9IB+fl5xdUgqUjU3Ny8suBYo5RQCIpMScHSDlnJBblZKZiUVCZX5kPMzkoNQWk1h9IpOSD9eEUjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t, State = _t, Toy = _t, Owner = _t]), ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Owner"}), GroupedRows = Table.Group(ReplacedValue, {"Color", "State"}, {{"All", each if List.Count(List.Select([Owner], (x)=> x = null)) = List.Count([Owner]) then Table.FirstN(_, 1) else Table.FirstN(Table.SelectRows(_, (x)=> x[Owner] <> null), 1), type table}}), CombinedAll = Table.Combine(GroupedRows[All]) in CombinedAll
5 Replies
you can use group by command to solve this.
use the next formula
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"State", type text}, {"Owner", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Color", "State"}, {{"Count", each Text.Combine(List.Distinct(([Owner])))}})
in
#"Grouped Rows"
result inUse the Group by Power Query Transformation and use a Max on Owner. Group by the other two fields. See screenshot
Thank you very much that worked well, but found another case, I will post it as a new question.
Example:
Color State Toy Owner Blue MI car Blue MI train Snoopy Yellow AZ ball Charlie Yellow AZ yoyo Red CO doll Red CO doll Desired Result: No matter what Toy is it, report only the row which has an owner if it exists.
Color State Toy Owner Blue MI train Snoopy Yellow AZ ball Charlie Red CO doll
This is more steps. I would personally try to push this logic back to the source but it can be done in power query.
To make this work in power query I had to create another copy of the table and join the original table to the copy. I also had to make a key with a merge on Owner, State, and, Color in both the copy and the original I used that to join in the merge step. Then I pulled the toy from the joined table.Hi heriberto_mb, check this:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfL1BBLJiUVAUilWB1W4pCgxMw9IB+fl5xdUgqUjU3Ny8suBYo5RQCIpMScHSDlnJBblZKZiUVCZX5kPMzkoNQWk1h9IpOSD9eEUjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t, State = _t, Toy = _t, Owner = _t]), ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Owner"}), GroupedRows = Table.Group(ReplacedValue, {"Color", "State"}, {{"All", each if List.Count(List.Select([Owner], (x)=> x = null)) = List.Count([Owner]) then Table.FirstN(_, 1) else Table.FirstN(Table.SelectRows(_, (x)=> x[Owner] <> null), 1), type table}}), CombinedAll = Table.Combine(GroupedRows[All]) in CombinedAll
