Dynamically adding data rows to dataset based on a condition

[allenind]

I am trying to modify my table1 to be able to dynamically add rows to the dataset based on the condition if one "Application Name" is not found in table1 but is present in Table 2, I will add the application name, make the version column to have the default value of 0.0 and just copy the computer name  from table2 to table 1. My tables look like this:

Table 1:

APPLICATION NAME	VERSION	COMPUTERNAME
Adobe Acrobat Reader DC	19.010.20069	COMPUTER1
Google Chrome	87.0.4280.141	COMPUTER1
Microsoft Edge	87.0.664.75	COMPUTER1
SnagIt	12.4.0	COMPUTER1
Adobe Acrobat Reader DC	18.010.20069	COMPUTER2
Google Chrome	85.0.4280.141	COMPUTER2
Microsoft Edge	84.0.664.75	COMPUTER2
SnagIt	11.4.0	COMPUTER2

Table 2:

APPLICATION NAME	VERSION
Adobe Acrobat Reader DC	19.010.20069
Google Chrome	87.0.4280.141
Microsoft Edge	87.0.664.75
SnagIt	12.4.0
Microsoft Visual Studio	10.0
Python Launcher	3.82

Expected Outcome:

NEW TABLE 1

APPLICATION NAME	VERSION	COMPUTERNAME
Adobe Acrobat Reader DC	19.010.20069	COMPUTER1
Google Chrome	87.0.4280.141	COMPUTER1
Microsoft Edge	87.0.664.75	COMPUTER1
SnagIt	12.4.0	COMPUTER1
Microsoft Visual Studio	0.0	COMPUTER1
Python Launcher	0.0	COMPUTER1
Adobe Acrobat Reader DC	18.010.20069	COMPUTER2
Google Chrome	85.0.4280.141	COMPUTER2
Microsoft Edge	84.0.664.75	COMPUTER2
SnagIt	11.4.0	COMPUTER2
Microsoft Visual Studio	0.0	COMPUTER2
Python Launcher	0.0	COMPUTER2

*note that the version of the newly added rows are 0.0 because I have a DAX column that will check if it is 0.0 it means the software is not installed in computer1, computer2 and so forth. 

Any help would be greatly appreciated, thank you!

[amitchandak]

@allenind , One option is  Merge in power query, Right Join.

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[allenind]

Hello @amitchandak, I have tried this and my expected outcome does not happen, it merges my two queries but It is not robust enough to be able to set a value of 0.0 to the version and to also copy the computer name value.

[Anonymous · Accepted solution]

Hi @allenind 

You can achieve your goal by power query or Dax.

My Sample Table is the same as yours.

DAX:

You can build a new calculated table.

New Table1 Dax = 
VAR _AppNameonlyinTable2 = SUMMARIZE(FILTER('Table 2',NOT('Table 2'[APPLICATION NAME] in VALUES('Table 1'[APPLICATION NAME]))),'Table 2'[APPLICATION NAME])
VAR _COMPUTERNAME = VALUES('Table 1'[COMPUTERNAME])
VAR _TRANSFORM = GENERATE(ADDCOLUMNS(_AppNameonlyinTable2,"VERSION","0.0"),_COMPUTERNAME)
VAR _NEW_TBALE1 = UNION('Table 1',_TRANSFORM)
RETURN
_NEW_TBALE1

Result is as below.

Power Query:

Merge Table2 and Table1 as a new table, select left Anti.

Then remove all column except APPLICATION NAME in new table.

Add a custom column "Version" = "0.0" in new table. (Rename as New Table 1)

Then we duplicate table1, remove all columns except COMPUTERNAME, right click and remove duplicate values.

Add a custom column in New Table 1. Custom = #COMPUTERNAME

Expand Custom column and rename as COMPUTERNAME.

Finally Append Newtable1 with Table1.

Result is as below.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.