Forum Discussion

DemoFour's avatar
DemoFour
Continued Contributor
6 years ago
Solved

Remove all data related to an application code, when the status equals an error code

I have a problem that I need to solve in Power Query before the data is loaded into Power BI. Whilst I can do the basic things here I have not learnt M yet.    In my data set I have a list of custo...
Need Help
  • Smauro's avatar
    Smauro
    6 years ago

    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].

DemoFour's avatar
DemoFour
Continued Contributor
6 years ago

Smauro 

 

Thank you for your assistance, I get errors if I paste the text into the advanced editor. 

I am trying to amend with the column names, but I get a Expression.Error: The import PreviousStep matchs no exxports. Did you miss a module reference? 

 

I am trying to amend the code, but I am not too sure what I am doing is correct. 

Can you expline how I would write the statement to remove the rows based on the values, or is it not that simple? 

Smauro's avatar
Smauro
Solution Sage
6 years ago

That's why I wrote that PreviousStep should be renamed to your previous step.

Let me try to give an example.When you open the advanced editor, you should see something ending like this:

...
   #"Changed Type" = ....
in
   #"Changed Type"

adding the new code, it should look like this:

...
   #"Changed Type" = ....
    ,SelectRelevant = Table.SelectColumns(#"Changed Type", {"Application No", "Current Status"}),
    Select950 = Table.SelectRows(SelectRelevant, each [Current Status] = 950),
    Distinct = Table.Distinct(Table.SelectColumns(Select950, {"Application No"})),

    Remove950 = Table.RemoveColumns(Table.NestedJoin(#"Changed Type", {"Application No"}, Distinct, {"Application No"}, "d", JoinKind.LeftAnti), {"d"})
in
    Remove950

 

Is that better?