Combining Two Queris

[Anonymous]

Good Afternoon 

I currently have to tables linking from a sharepoint list

Resource Capacity

Resource Demand

Withing both the tables there is only a From Date and a To Date with the percentage required with the Resource Name and Resource ID

I then Created queries for both 

Resource Per Week

Capacity  Per Week 

In wich I broke down the Date From - Date To to give me week etc that I can work with

. 

I would now like to create a graph

Where I can bring Resource and capacity per week together so I can create a grap simular to the below listed (sample from internet)

Is ther a possiblity to merge the two table by Via Resource ID to give me the capacity / demand for that week. 

I dont see the option when i go to my query editor.  

Any guidance would be appreciated. 

[tarunsingla]

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.

[Anonymous]

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

[edhans · Accepted solution]

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.

[edhans]

Roughly you would do the following:

1. 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".
2. Format both of those fields as text.
3. You should now have two queries with the same columns.
4. 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.
5. 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.
6. 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.
7. 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.