Eliminate similar rows based on a condition in a particular field.

[zacksg1]

In a particular table in Power Query, I have several (say 5) demographic fields, a TRUE/FALSE field, an attribute field, and a value field. I am creating a pivot table (such that the entries in the attribute field become field headers). There are a few situations in which two separate rows will match in each of the demographic fields plus the attribute field, so there will be a conflict about which row's value entry field entry will become the value in the pivot table.

I want to resolve this conflict by picking the value corresponding to the row with a TRUE value (there will only be one in each of these situations).

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]FirstName[/TD]
[TD]LastName[/TD]
[TD]Birthdate[/TD]
[TD]TRUE/FALSE[/TD]
[TD]Attribute[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]First1[/TD]
[TD]Last1[/TD]
[TD]Birthdate1[/TD]
[TD]TRUE[/TD]
[TD]A[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]First1[/TD]
[TD]Last1[/TD]
[TD]Birthdate1[/TD]
[TD]FALSE[/TD]
[TD]A [/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]First1[/TD]
[TD]Last1[/TD]
[TD]Birthdate1[/TD]
[TD]FALSE[/TD]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]First2[/TD]
[TD]Last2[/TD]
[TD]Birthdate2[/TD]
[TD]TRUE[/TD]
[TD]B[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]First3[/TD]
[TD]Last3[/TD]
[TD]Birthdate3[/TD]
[TD]FALSE[/TD]
[TD]A[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I want a table that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]FirstName[/TD]
[TD]LastName[/TD]
[TD]Birthdate[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]First1[/TD]
[TD]Last1[/TD]
[TD]Birthdate1[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]First2[/TD]
[TD]Last2[/TD]
[TD]Birthdate2[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]First3[/TD]
[TD]Last3[/TD]
[TD]Birthdate3[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The A=7 and the A=9 in the original table conflict for person #1, but because the 7 is in a TRUE row, I want to select the 7 instead of the nine.

How can I do this?

Note that I can't just filter out FALSE rows, since I do want to keep the FALSE values (in situations where there is no TRUE value it conflicts with)--the A=1 value for person #3, for example.

Thanks!

 

[scottsen]

Try Pivoting the TRUE/FALSE column against the Value column... felt like I was getting somewhere w/ that...?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = let #"Changed Type" = Table.TransformColumnTypes(Source, {{"TRUE/FALSE", type text}}, "en-US"),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"TRUE/FALSE"]), "TRUE/FALSE", "Value", List.Sum)
 in #"Pivoted Column",
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Value", each if [true] = null then [false] else [true]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"true", "false"})
in
    #"Removed Columns"