Forum Discussion
Remove all data related to an application code, when the status equals an error code
I'm glad it worked. You should mark it as a solution in case anyone is searching something similar.
You've mostly understood the code, I'll try to explain it step by step
SelectRelevant = Table.SelectColumns(#"Changed Type", {"Application Number", "Current Status"}),We get only the 2 columns we're going to work with, to reduce data load.
Select950 = Table.SelectRows(SelectRelevant, each [Current Status] = 950),We filter the relevant columns for only rows where status is 950
Distinct = Table.Distinct(Table.SelectColumns(Select950, {"Application Number"})),We get only the [Application Number] column, and because we need any [Application Number] just once, we make it distinct.
Remove950 = Table.RemoveColumns(Table.NestedJoin(#"Changed Type", {"Application Number"}, Distinct, {"Application Number"}, "d", JoinKind.LeftAnti), {"d"})We join our table as it was before these calculations, with the newfound Distinct table, keeping only non-matches (LeftAnti means anything from left table (#"Changed Type") which didn't match the right table (Distinct)), and subsequently we remove the extra column [d].
It worked thank you, it removed the two applications created in error. So will now remove all new data errors from the data.
to work this backwards as I do not know M
Select950 used Table.SelectRows(SelectRelevent each = 950 This is used to look in the relevent column and select the specifiyed value and select each one of them
Distinct used Table.Distinct(Table.SelectColumns(Select950 Did this then look for all the distinct Application numbers linked to the 950 row ?
Remove950 - I am not too sure here - but this is using the distinct application number to remove all rows associated with the 950 code and then removing all the associated application number rows??
thank you again for your time and patience today
I'm glad it worked. You should mark it as a solution in case anyone is searching something similar.
You've mostly understood the code, I'll try to explain it step by step
SelectRelevant = Table.SelectColumns(#"Changed Type", {"Application Number", "Current Status"}),
We get only the 2 columns we're going to work with, to reduce data load.
Select950 = Table.SelectRows(SelectRelevant, each [Current Status] = 950),
We filter the relevant columns for only rows where status is 950
Distinct = Table.Distinct(Table.SelectColumns(Select950, {"Application Number"})),
We get only the [Application Number] column, and because we need any [Application Number] just once, we make it distinct.
Remove950 = Table.RemoveColumns(Table.NestedJoin(#"Changed Type", {"Application Number"}, Distinct, {"Application Number"}, "d", JoinKind.LeftAnti), {"d"})
We join our table as it was before these calculations, with the newfound Distinct table, keeping only non-matches (LeftAnti means anything from left table (#"Changed Type") which didn't match the right table (Distinct)), and subsequently we remove the extra column [d].