Power Query - get values from other column and remove responding values

0
votes

I am looking for a way retrieve the following in power query:

enter image description here

Look into column A for values = 0 get the responding number C and remove all responding numbers from the column C

It would be great if someone could give an advise how to achive this.

Thanks!

2
powerbipowerquery

2 Answers

0
votes

Filter column A equal to zero using the drop down arrow atop column A

Merge the results back onto the original data set (requires a second table, or in example below, manual adjustment to code using home ... advance editor... )

Expand merged column C; will show data next to any row where A=0 for same C

Add custom column to remove any data in C where original C = merged C

=  if [C]=[C.1] then null else [C]

Remove extra columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([A] = 0)),
#"Merged Queries" = Table.NestedJoin(Source,{"C"},#"Filtered Rows",{"C"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"C"}, {"C.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Table2", "New.C", each if [C]=[C.1] then null else [C]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"C", "C.1"})
in #"Removed Columns"
0
votes

I solved this by

  • creating a new query and and in this created an "exclude" list, by copying the content from advanced editor to the exclude list
  • filtering the Null values
  • merging as new query "left anti join"
  • and all the Null values and responding numbers are gone.

@Horseyride Thanks again for the hint