Our task is to consolidate multiple queries in single source - one consolidation table. Note that at this example we use MS Excel (product Microsoft 365).
Navigate to tab “Home” then find “Merge Queries”: we have two options #1 Merge Queries, #2 Merge Queries as New:
Option #1 Merge Queries. It is means that data from second query will add to active query in Editor.
Option #2 Merge Queries as New. This function for create new one query that will contain data from first and second query.
Click second option. We recommend using “Merge Queries as New”. This option allows save previous sources of data and if you need to change a logic of their creation it will be not so difficult because changes in them do not influence on the structure of current working table that can contain additional calculate columns, data transformation functions which reference to specific columns.
There are 6 types of table join:
In our case we will use Left Outer Join:
Next step is selection a key column from table sales and a key column from table manager. Note that the table sales and the table manager are queries which result in form of table.
Before the join of queries we have to check a format of our key columns. We have to use columns with the same type:
After properties set you should click ok button:
Select a target column "Name":
Example of Right Anti Join: imagine that we need to obtain managers who does not have sales.
Example of Left Anti Join: imagine that we need to check if we have sales with no manager.
Image that you need consolidate information about sales of your shops. The input file is the same. It has identical columns names, columns order. In our case we could use Append Queries (full path Home tab->Append Queries->Append Queries as New):
As for we have more then two tables we will use second option – “Three or more tables”:
Example of Full Join: our task to find all items which are in stock but had not sold yet. We could use table about items (items) and our sales tables by each shop (shops_data) that contains information about sales by items.
By first look we see rows with null value by shop with id=2,3,4. Temprorary apply filter by column ItemName with value null we see that the reason is manager with id=6 has no sales at these shops.
Then apply filter to column ItemName and deselect rows with nulls.
Our task is completed: chess is in stock but had not sold yet.
Example of Right Outer Join: imagine that we need to return managers who has no vacations.
We obtain list of managers and see that Tom and Lucy at work. But where is Brian? This table does contain information about him because at the table manager_status no information about him. Probably we should call to him and ask where he is now. Remember Right Join return only rows from second table and matching rows from first one.
Example of Inner Join: after investigation we know where Brian is. Because of he has got a flue we need temporary replace him by another manager. In this case we have to find manager who has sales at shops where works Brian and also has the same items sold. At this point we have two conditions: 1# shop id, 2# item id must be equal. We take table with description of sales (shops_data_with_description) and join it twice (example of self join). Note we will have reference to one query at this example, only to shops_data_with_description.
In result table we get all managers with the same parameters (shop id, item id). Also we see that Brian is at result query (ManagerID=5).
We see that there are not Brian records, but there are new records with nulls. This is because at manager_status table - not all managers fit our parameters. But if you remember we used Right Join (to return all from the second table). To fix this we need apply filter on null records:
To add information about shop name and manager's name we have our result table. We see that Nancy at vacation, Tom at work. Thus we could estimate Brian's plan of sales 5 christmas tree to Tom. He sold only 1 christmas tree according sales statistics.
Our tutorial is completed. See new examples for Power BI and Power Query at https://virtualanalytics.azurewebsites.net/ Thank you!