locked
Filter a table: with Table.Join or Table.SelectRows or something else RRS feed

  • Question

  • Hi everyone

    (Quite new to Power Query). Basically I need to filter the Data table based on the match between the Source field and the Sources in the 2nd table (what I called Filter)

    So far I have the following 2 scripts working and I wonder which one (or anything else) I should use??? In real world the number of "valid" Sources is around a hundred while the number of rows in the Data table  will likely reach the million of rows by the end of each year

    Script 1:

    let
        tblData = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
        tblSources = Excel.CurrentWorkbook(){[Name="tblSources"]}[Content],
        tblDataChanged = Table.TransformColumnTypes(tblData,{{"Date", type date}, {"Source", type text}}),
    
        tablesJoined = Table.Join(tblData, {"Source"}, tblSources, {"Sources"}, JoinKind.RightOuter),
        tblOut = Table.RemoveColumns(tablesJoined,{"Sources"})
    in
        tblOut

    Script 2:

    let
        tblData = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
        tblSources = Excel.CurrentWorkbook(){[Name="tblSources"]}[Content],
        tblDataChanged = Table.TransformColumnTypes(tblData,{{"Date", type date}, {"Source", type text}}),
    
        lstSources = Table.ToList(tblSources),
        tblOut = Table.SelectRows(tblDataChanged, each List.Contains(lstSources, [Source]))
    in
        tblOut

    Any thought and suggestion will be highgly appreciated. Thanks in advance

    Wednesday, March 28, 2018 1:17 PM

Answers

  • Hey,

    A table join would be the best fit for your case. Specifically the right join so you can only get the values that are on your "filter" table.

    • Proposed as answer by Maxim ZelenskyMVP Wednesday, March 28, 2018 3:49 PM
    • Marked as answer by Lz._ Wednesday, March 28, 2018 4:41 PM
    Wednesday, March 28, 2018 2:41 PM

All replies

  • Hey,

    A table join would be the best fit for your case. Specifically the right join so you can only get the values that are on your "filter" table.

    • Proposed as answer by Maxim ZelenskyMVP Wednesday, March 28, 2018 3:49 PM
    • Marked as answer by Lz._ Wednesday, March 28, 2018 4:41 PM
    Wednesday, March 28, 2018 2:41 PM
  • @Miguel Escobar

    I suspected Table.Join(...JoinKind.RightOuter) was the way to go but as newbie I pref. to ask.

    Thanks much Sir! 

    Wednesday, March 28, 2018 4:41 PM