Forum Discussion

NBOnecall's avatar
NBOnecall
Helper V
6 years ago

Merge Queries with a filter first?

I have two tables one is being brought in as a DirectQuery and one table is being importated.


The table that is being imported looks something like this,

 

I just want the results of LocationID 4, so I would just filter on that in the query editor. My second table (DirectQuery) though, is what I want to do the lookup function in PowerBI off of internal ID. It looks something like this,

 

 

I want to appened the loction 4 phase out date to this DirectQuery import. If it wasn't a direct query I could just create a calculated column with the lookup fuction bringing back my desired result. From my research I can't do that with a DirectQuery.

 

Is there a way to merge a query in Edit Queries that would first filter on Location 4 before merging to the DirectQuery table?

 

Thank you.

Need Help

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi NBOnecall ,

     

    We can try to add a step in the Table 2 (DirectQuery) use Table.Join Function to meet  your requirement:

     

    = Table.Join(#"Table1","ItemID", Table.SelectColumns(Table.SelectRows(#"Table2",each [LocationId]=4),{"ItemID","PhaseoutDate"}),"ItemID")

     

    All the Query of Table1

     

    let
    Source = Sql.Databases("localhost"),
    TestDatabase = Source{[Name="TestDatabase"]}[Data],
    #"Table1" = PBITest{[Schema="dbo",Item="Table1-Import"]}[Data]
in
    #"Table1"

     

    All the Query of Table 2

    let
    Source = Sql.Database("localhost", "TestDatabase"),
    #"Table2" = Source{[Schema="dbo",Item="Table2-DQ"]}[Data],
    #"Appended Query" = Table.Join(#"Table2","ItemID", Table.SelectColumns(Table.SelectRows(#"Table1",each [LocationId]=4),{"ItemID","PhaseoutDate"}),"ItemID")
in
    #"Appended Query"

     

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NBOnecall's avatar
      NBOnecall
      Helper V
      6 years ago

      v-lid-msftthank you for your help, greatly appericiate it.

       

      I am confused about your first step. Where is this supposed to happen/how?

       

       

      = Table.Join(#"Table1","ItemID", Table.SelectColumns(Table.SelectRows(#"Table2",each [LocationId]=4),{"ItemID","PhaseoutDate"}),"ItemID")

       

       

      New to the whole using the advance editor.

       

      • NBOnecall's avatar
        NBOnecall
        Helper V
        6 years ago

        For some reason my edit didn't work, but to add to my confusion this is my query for Table 2 or "ns_vwInventorySnapshot" -

         

        let
    Source = Sql.Databases("DW"),
    DataWarehouse = Source{[Name="DataWarehouse"]}[Data],
    ns_vwInventorySnapshot = DataWarehouse{[Schema="ns",Item="vwInventorySnapshot"]}[Data],
    #"Grouped Rows" = Table.Group(ns_vwInventorySnapshot, {"Date", "ItemInternalId", "Name"}, {{"Available", each List.Sum([Available]), type number}, {"OnHand", each List.Sum([OnHand]), type number}, {"OnOrder", each List.Sum([OnOrder]), type number}, {"InTransit", each List.Sum([InTransit]), type number}, {"AverageCost", each List.Sum([AvgCost]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ItemInternalId"}, #"ns vwFirstDateInStockByLocation", {"ItemInternalId"}, "ns vwFirstDateInStockByLocation", JoinKind.LeftOuter),
    #"Expanded ns vwFirstDateInStockByLocation" = Table.ExpandTableColumn(#"Merged Queries", "ns vwFirstDateInStockByLocation", {"FirstDateInStock"}, {"ns vwFirstDateInStockByLocation.FirstDateInStock"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded ns vwFirstDateInStockByLocation",{{"ns vwFirstDateInStockByLocation.FirstDateInStock", "FirstDateInStock"}})
in
    #"Renamed Columns"

         

        I understand that I have to put this code into the bottom of the query.

          #"Appended Query" = Table.Join(#"Table2","ItemID", Table.SelectColumns(Table.SelectRows(#"Table1",each [LocationId]=4),{"ItemID","PhaseoutDate"}),"ItemID")
in 
 #"Appended Query"

         

        WhatI don't understand is how to also put #"Apeended query" also in it as it gives me an error because I believe #"Renamed Columns" is also in that section.