Power query: Removing rows in several tables based on a master table

[IHam]

I work in a college so every year pupils leave and I wish to remove their data from several tables before passing it into power bi to shorten refresh time.

I have a table of current pupils with unique identifier codes. How can I use that to delete rows in other tables that do not match the current table. I.E. only keep the data of current puils and delete the rest in the other data tables?

many thanks

[Anonymous]

Go to advanced query editor and add the function Table.SelectRows() with relevant parameters to filter the rows and load the relevant ones. Please check the online documentation if you are unfamiliar with power query. 

From what I understand, you are loading all the rows and want to filter it before it loads to reduce the loading time. So it will be better you add the filter criteria into the Power query itself.

[IHam]

Thanks very much, so in the table i want to reduce, (which isn't the mastertable). Into the query record,  I insert Table,SelectRows(mastertable,.... ) and then how do i get it to select the rows that match those in the mastertable and then delete them?

the  examples seem to go though particular rows but as I have a dynamic master table I will need a blanket deletion of the rows in the other tables.

Hope this makes sense!

thanks again

[Anonymous]

Hi,

There are many methods... With the limited amount of information, I can suggest the following...

Functions to use...

1) Table.Column() // this will give you a table's field as a list.

2) List.Distinct() // removes duplicates from a list.

3) List.Difference() // compares two lists and gets the difference.

4) List.Contains() // checks a list if an item is in the list. Returns TRUE or FALSE

5) each Record.Field(_,<fieldname>) this should be used for iterating over the table and compare the current value to something.  for example

Table.SelectRows(<tablename>, each List.Contains(masterlist, Record.Field(_,<fieldname>)) = TRUE)

 this will iterate on tablename  and compares the <fieldname> with the items in the list masterlist and if List.Contains is TRUE, it will select the row or it will filter it out.

The 'each' keyword is for iteration over the table and the '_' underscore in Record.field is for selecting the current record of the table that you are iterating.

If you combine all these functions to get a list of current pupils and compare the pupil id of the table using Table.SelectRows(<tablename>, each List.Contains(masterlist, Record.Field(_,<fieldname>)) = TRUE) and filter accordingly, i think you will get the desired results.

[IHam]

Smashing- I will have a play with those and see what i can do. Thanks for your help.

[Anonymous · Accepted solution]

Then go through the following example...

Assume you have two tables.

Table 1: PupilMaster

PupilID	CurrentFlag
A	Yes
B	Yes
C	No
D	No
E	No

Table 2: PupilRecords

PupilID	Data
A	A's Records
B	B's Records
C	C's Records
D	D's Records
E	E's Records

Objective: Load only A's and B's records.

Power Query of PupilMaster

let
    Source = Excel.Workbook(File.Contents("C:\Users\Pupils.xlsx"), null, true),
    PupilMaster_Sheet = Source{[Item="PupilMaster",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PupilMaster_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PupilID", type text}, {"CurrentFlag", type text}})
in
    #"Changed Type1"

This is automatically generated when I loaded a table from excel. Don't bother about the contents. It does not matter.

PowerQuery for PupilRecords

let
    Source = Excel.Workbook(File.Contents("C:\Users\Pupils.xlsx"), null, true),
    PupilMaster_Sheet = Source{[Item="PupilMaster",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PupilMaster_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PupilID", type text}, {"CurrentFlag", type text}})
in
    #"Changed Type1"

Based on these two automatically generated power query of the two tables, we will create the following query which will load only filtered records of A & B.

let
    P1 = let
    Source = Excel.Workbook(File.Contents("C:\Users\Pupils.xlsx"), null, true),
    PupilMaster_Sheet = Source{[Item="PupilMaster",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PupilMaster_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PupilID", type text}, {"CurrentFlag", type text}})
    in
    #"Changed Type1",

    P2 = let
    Source = Excel.Workbook(File.Contents("C:\Users\Pupils.xlsx"), null, true),
    PupilRecords_Sheet = Source{[Item="PupilRecords",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(PupilRecords_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PupilID", type text}, {"Data", type text}})
    in
    #"Changed Type1",

// Added codes Gets the current list of Pupils as a list.

    PupilList = Table.Column(
                    
                    Table.SelectRows(P1,each Record.Field(_,"CurrentFlag")="Yes"),
                    "PupilID"),
//Filters your Records table to get the desired output.

    FilteredRecords = Table.SelectRows(P2,each List.Contains(PupilList,Record.Field(_,"PupilID")))
  
in
    FilteredRecords

Query Output

PupilID	Data
A	A's Records
B	B's Records

[IHam]

Wow, great thanks - I will have a play with the last bit of code. I presume you will need to pass a different table to the model in power bi. In other words pass the filtered table which will have a different name and then re-set up the connections in power bi. You can't delete the records in situ in the table but need create a separate filtered table?

[Anonymous]

Not exactly. You don't have to reset the connections. You don't need an additional table. You could modify your existing table's power query to filter the records. All your connections may remain intact.

The logic here is that you will nest 2 Let-in statements inside an outer let in. One of the inner let-in is for getting the list of current students from your pupilmaster and the second let-in will be your data that you want' to filter. The outer let-in will filter the data and give you the output.