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.
Take a look at the merge feature:
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries
Would help if you can share sample data from your two tables.
Thank you for your responses.
I had to create a merged query with another table called Calander. This allowed me to break my limited days information down to weeks and days.
Intially I had 2 x Tables
ResourceProjectCapacity
ResourceProjectDemand
I then created 2 x new tables called
Resource Per Week
Capacity Per Week
These were mereged with a Calander table to break down the days from "DateFrom - DateTo" to single line for everyday with the
| Date From | Date To | Percentage Required | PRJID | Project | ResourceID | %PerWeek | Date | Week No | WeekEnding | ResourceType |
| 24/01/2020 | 31/12/2020 | 100% | 6 | SAP | 141 | 100.00% | 24/01/2020 | 2020-04 | 26/01/2020 | Demand |
| 24/01/2020 | 31/12/2020 | 100% | 6 | SAP | 141 | 100.00% | 25/01/2020 | 2020-04 | 26/01/2020 | Demand |
| 24/01/2020 | 31/12/2020 | 100% | 6 | SAP | 141 | 100.00% | 26/01/2020 | 2020-05 | 26/01/2020 | Demand |
| 24/01/2020 | 31/12/2020 | 100% | 6 | SAP | 141 | 100.00% | 27/01/2020 | 2020-05 | 02/02/2020 | Demand |
| 24/01/2020 | 31/12/2020 | 100% | 6 | SAP | 141 | 100.00% | 28/01/2020 | 2020-05 | 02/02/2020 | Demand |
The talbes were created by usting the following
ResourcePerWeek = FILTER(CROSSJOIN('Resource Project Demand','Calendar'),'Calendar'[Date]>='Resource Project Demand'[Date From ]&&'Calendar'[Date]<='Resource Project Demand'[Date To])
I would now like to bring those to together - When I go back to query editor I can not see the two created in my left hand pane I only see it when I exit queries editor so no option to merge them togehter.
Thank in advance for all help. Still learning everyday how to use Powerbi
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.