Merge queries?

[FearDerBeard]

Have I concluded correctly that I should use Merge Queries functionality to filter the results of another query? 

In my scenario I have a list of transactions that can easily filter by date.  I only want to return the subset of customer records associated to those filtered transactions.  This way I only include the customer attributes who were active in that period.

In SQL you might use a join on the two tables then filter on the date or a IN function with a subquery.

Transaction Table from source

CustomerID	TransactionDt	Amount
1	2/2/2017	100
1	2/3/2017	75
1	2/4/2017	450
3	2/2/2017	100
3	2/4/2017	5
3	2/4/2017	25

Customer Table from source

Customer	Name
1	Tony
2	Bob
3	Stewart

Customer table filtered to model

Customer	Name
1	Tony
3	Stewart

I basically don't want to include 1MM customer attribute records when I only need 10K.

I'm using merge query left outer, but it is a total dog.

Thanks

[FearDerBeard · Accepted solution]

Ok. Here's the work around I found.  Perhaps ineloquent, but functional. 

1. I created a view that performed the join between the transaction table and customer table with everything from the customer and only the transaction date from the transaction table. 

2. This was sourced and navigated to in Power Query where I filtered the transaction date like I did for the initial transaction table.

3. I removed the transaction date column from the grid in Power Query using "Remove Columns".

4. I then removed duplicate rows in Power Query using "Remove Rows".

This ran quickly and deliver only the unique customers necessary.  I don't know what caused the problem, but if I had to guess it's likely Oracle and Power BI weren't playing nicely which prevented query folding to occur.

[FearDerBeard]

I came up with this query, but it keeps pulling back more records than filtered:

let
    Source = Oracle.Database("myschema", [HierarchicalNavigation=true]),
    myschema= Source{[Schema="myschema"]}[Data],
    Transaction= myschema{[Name="Transaction"]}[Data],
    Customer1= myschema{[Name="Customer"]}[Data],

    #"Filtered Rows" = Table.SelectRows(Transaction1, each [TransactionDt] = PeriodParameter),

    Merge = Table.NestedJoin(#"Filtered Rows",{"CustomerID"},Customer1,{"CustomerID"},"NewColumn",JoinKind.Inner)
in
    Merge

[FearDerBeard]

Ok.  I  changed the inner to leftouter.  It returns all the records correctly, but when I remove/include columns it starts to tank in performance.  Hours.

[SaritaSenapati]

Hi @FearDerBeard,

Well I guess you can first create a refrence of your transaction table->delete the amount column->left outer join with the customer table in power BI -> click on the transaction table column-> and finally select the date filter you need.

Let me know if this answers your question.

Regards,

Sarita

[FearDerBeard]

Hmm.  I tried something like that in SQL by joining the two tables to merge the date to the customer table, but when I apply the filter operation it ends up bringing back the full results of the query rather than just the filtered results desired. Here's the code:

let
    Source = Oracle.Database("ODSPROD", [Query="SELECT C.*, Y.TransactionDt FROM Customer c  JOIN Transaction Y ON Y.CustomerId = P.CustomerId", HierarchicalNavigation=true]),
    #"Filtered Rows" = Table.SelectRows(Source, each [ACADEMIC_PERIOD] = AcademicPeriod)
in
    #"Filtered Rows"