Join 2 Tables and replace values in the master table based on data in exception table

[febing]

Hi

I am pretty new to Power BI. Have a very basic question. I am preparing a roster report on a calendar matrix view based on excel data.

I have 2 tables - Master Roster Table which gives the data for a month and then there is a Roster Exception table. My requirement is I need to merge these 2 tables using employeeid and Schedule date as the join criteria and if records are found in the roster exception table, then the master roster table should be updated with these column values and new flag to be introduced saying that this is an exception roster.

How can we achieve this. I know its a very basic quetion. Pretty new to these things. Any pointers will be of great help

Regards

Febin Gafoor

[jgeddes · Accepted solution]

Here is an example that might help...
If you have two tables such as,

'masterTable'

'exceptionTable'

You can merge the 'exceptionTable' into the 'masterTable' using the ID and Date columns as the key.

Expand only the exceptionValue in the merged column.

Your table should now look something like...

Add a column that determines which table's value we will end up with.

Now select the 'exceptionValue' column and choose 'Replace Values' from the 'Transform' ribbon...
Use 'null' as the value to find and a random number as the 'Replace With' value. It does not matter what number as we are changing it right away.

The function value should populate with...

Replace the '99999' with 'each [masterValue] and you should end up with...

You can now change remove the masterValue column if you do not need it for other purposes, change data types and rename columns as desired to end up with...

Hope this helps.

[TDK]

Hi Febin,

So I would sugest the following (assuming you are using PBI Desktop):

1. When inside the Power Query Editor under Transform Data - Go to and click on your Master Roster query and look on the ribbon at the top of the screen, under the Home tab to the right you shoul see Merge Queries click on that.
2. From there you should be presented with a merge wizard, select the Roster Exception table from the dropdown and then on the preview tables CTRL+click on the colums employeeid and Schedule date you want to join on. You will notice it highlights the column and there should be a small number next to the column name. The order here is important. Then check if the join is set to Left Join. Click ok
   
3. After a bit of time you should now see a table with an additional column with the word "table" in bold. Next to the column you should see a icon that looks like 2 arrows pointing away from eachother, click on that and this should enable you to expand the data from the joined table. Note! if there is more than one row from the Roster Exception that matches Master Roster you will create duplicate records in your Master Roster.
4. Select the columns you care about and expand them, you should see nulls where you did not have any matches.
5. You can then create a custom column, on the ribbon under the Add Column tab select Custom Column. Give the column a name like Exception Flag and then in the formula bar type:
   <column name> = null
   Where <column name> is the coumn you are checking for nulls, this will generate a True or False that you can use as your flag

That should be it.

Hope it helps