Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Note that all these are happening within powerquery before Close & Apply.
Let's say I have 2 tables (Sales left join Employees on Employee_ID)
After Merge Queries, Sales will be overwritten in place with the merged table containing more columns on Employee details.
I then filter some employees through the Employee query (Not the merged Sales query) and realized more nulls appeared on Employee columns (eg. Employee Name) in the merged table. Total number of rows remain the same since it's a Left Join, not inner, so Sales determines row count.
This tells me the merge operation knows that the filter on employees happened, even though i did the filtering after the merge.
How did this work?
Within a query, we can see a sequence of Applied Steps, but what's the order in this case when Applied Steps among 2 queries are interacting?
Is there some precedence, like merge operations always happen last?
Is this concept similar (or based on the same engine) to how visuals on report view filter propagate based on table relationships?
I assume the difference is in report view, the changes are temporary (users can uncheck and check back slicer options to recover data), while in powerquery, once applied, there's no easy way of getting back what's excluded from the filter without opening powerquery again.
What made you decide to merge? Why not let the data model do the work for you?
I was following a Datacamp course (Data Transformation in PowerBI).
Is there no reason to do merging or appending in power query?
Assuming there isn't, I wonder if there are other operations that combines tables, in which case the question remains.
There is no good reason to merge in Power query, only various shades of bad reasons. Power Query runs on disk and merges tend to slow down the process substantially. Power BI / DAX runs in memory and is substantially faster when you utilize the data model. Your first choice should be to utilize the data model.