Forum Discussion
Merge queries?
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"