CODEWITHSUNDEEP
powerbidaxpowerquery
adam Wadsworth
adam Wadsworth

Reputation: 784

power BI need to find missing values in table from a reference list for each group of values

I need to find where values are missing in a table from a reference table and then create an output with the missing values. Here is an example of what I'm trying to do.

enter image description here

Does anyone have any idea how to do this.

Upvotes: 0

Views: 296

Answers (1)

horseyride
horseyride

Reputation: 21393

your sample output is wrong, but your question implies this answer

It finds the unique values of area, puts that up against every value in reference table, then uses left anti join merge to find items in one table that are not in another table

// assumes existence of ReferenceTable and DataTable queries
let Source = Table.AddColumn(ReferenceTable,"Area", each List.Distinct(DataTable[Area])),
#"Expandedxx" = Table.ExpandListColumn(Source, "Area"),
#"Merged Queries" = Table.NestedJoin(#"Expandedxx", {"Type", "Area"}, DataTable, {"Type", "Area"}, "DataTable", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"DataTable"})
in #"Removed Columns"

Upvotes: 1

Related Questions