Forum Discussion

allenind's avatar
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 app...
Need Help
Tutorial Requests
  • Anonymous's avatar
    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. 

allenind's avatar
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's avatar
    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.