Forum Discussion
Dynamically adding data rows to dataset based on a condition
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_TBALE1Result 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.
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.
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.