Forum Discussion
Merge queries?
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
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.
5 Replies
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.
Hi Anonymous,
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
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"
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
