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 customer actions linked to the same application number, that is static for all actions.

The actions are listed as a status change number, and depending on the actions you can have many numbers relating to the action stages.

 

Customer     Application No          Current Status     Date Set

Dave             1                                200                      01/01/01

Geoff            2                                200                      01/01/01

Dave             1                                300                      02/01/01

Dave             1                                950                      02/01/01

Geoff            2                                300                      02/01/01

Simon           3                                200                      03/01/01

Geoff            2                                400                      02/01/01

Dave             4                                200                      03/01/01

 

If an application has been undertaken in error, it is changed to status 950. People can add duplicated applications to the system and we cannot delete them only change to 950 created in error

I have filtered the curent status column to remove 950 - However all other status numbers related to the application are still brought through into power BI, giving me duplicate applications that I do not need. 

What I want to do is: 

If an Application status changes to 950 then remove this application and all rows of data linked to this application in the sheet.

 

Is there a way to remove all the rows of data linked to the application number if the status code reaches 950? 


Thank you for any asssitance you can offer

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

10 Replies

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago

    Hi DemoFour 

     

    You could try editing your query in the advanced editor, adding this:

        ,SelectRelevant = Table.SelectColumns(PreviousStep, {"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(PreviousStep, {"Application No"}, Distinct, {"Application No"}, "d", JoinKind.LeftAnti), {"d"})
in
    Remove950

    after the last step and before the 'in'.

    PreviousStep should change to the previous step's name.

     

    Cheers

    • 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?