Forum Discussion
Combining Two Queris
Tables created in the DAX/Report View of Power BI are not accessable by Power Query. Power Query is a one way street - from external sources to the DAX model if you choose to load them. You cannot go from the DAX model to Power Query.
To do a Cartesian Join (Crossjoin) in Power Query, you simply create a custom column in your first query and the formula is =#"Your Second Query" and then expand that table. It will do the full join. You can see my personal notes on this here.
If you can push back all transformation steps into Power Query, it will be easier to fully model your data. The only thing I ever really model in DAX is when working with SSAS Tabular where Power Query isn't available, or very rarely a calculated column if I'm in a hurry and don't want to go back into PQ. But that is usually ad hoc and not for production.
Roughly you would do the following:
- In Power Query, add a custom column to each called "Resource Type" and in one just type the formula ="Resource Demand" and in the other ="Resource Capacity".
- Format both of those fields as text.
- You should now have two queries with the same columns.
- Select one of the queries and on the Home ribbon, select the Append menu dropdown, and select "Append as New Queries" and name this some useful name for your query. It will be called Append1 by default.
- Make sure the new table looks right. If you had any differences in column names, they will show up here as separate columns. Go back to the original queries and fix those column names there. Come back to Append1 and it will automatically fix itself.
- Now, right-click on the first two queries and make sure "Enable Load" is NOT checked, but that your Append1 query is set to load.
- Now just drop the fields into a matrix view or whatever field you want. By putting all of these in one normalized table, your Resource Type is just another field to slice and dice on.