Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |