Forum Discussion
Combining Two Queris
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.
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.
4 Replies
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.
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.
