Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
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 in
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
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
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 in
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.
Use 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 |