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 NAMEVERSIONCOMPUTERNAMEAdobe Acrobat Reader DC19.010.20069COMPUTER1Google Chrome87.0.4280.141COMPUTER1Microsoft Edge87.0.664.75COMPUTER1SnagIt12.4.0COMPUTER1Adobe Acrobat Reader DC18.010.20069COMPUTER2Google Chrome85.0.4280.141COMPUTER2Microsoft Edge84.0.664.75COMPUTER2SnagIt11.4.0COMPUTER2 Table 2: APPLICATION NAMEVERSIONAdobe Acrobat Reader DC19.010.20069Google Chrome87.0.4280.141Microsoft Edge87.0.664.75SnagIt12.4.0Microsoft Visual Studio10.0Python Launcher3.82 Expected Outcome:NEW TABLE 1APPLICATION NAMEVERSIONCOMPUTERNAMEAdobe Acrobat Reader DC19.010.20069COMPUTER1Google Chrome87.0.4280.141COMPUTER1Microsoft Edge87.0.664.75COMPUTER1SnagIt12.4.0COMPUTER1Microsoft Visual Studio0.0COMPUTER1Python Launcher0.0COMPUTER1Adobe Acrobat Reader DC18.010.20069COMPUTER2Google Chrome85.0.4280.141COMPUTER2Microsoft Edge84.0.664.75COMPUTER2SnagIt11.4.0COMPUTER2Microsoft Visual Studio0.0COMPUTER2Python Launcher0.0COMPUTER2 *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!

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.

allenind

Frequent Visitor

5 years ago

Solved

Dynamically adding data rows to dataset based on a condition

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!

Need Help

Tutorial Requests

Anonymous
5 years ago
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.

3 Replies

amitchandak
Super User
5 years ago
allenind , One option is Merge in power query, Right Join.

https://radacad.com/append-vs-merge-in-power-bi-and-power-query
- allenind
  Frequent Visitor
  5 years ago
  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
    Not applicable
    5 years ago
    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.

Forum Discussion

Dynamically adding data rows to dataset based on a condition

3 Replies

Related content

dynamic conditional formatting

Dynamical conditional formatting for multiple columns

Refresh dataset api

Conditional index based on dataset in one column

Dynamic Filter