Forum Discussion

MP-iCONN's avatar
MP-iCONN
Resolver I
4 years ago
Solved

Replace Values based on Matching Columns

I am facing an issue where I can not figure out how to change, in Power Query, the value of one columns based on certain matches in others.

 

For example I have 5 columns, Employee ID, Name, Company, Network Account, Email Account.  What I need to do is if the column Company has Both in it then changed the value of the other matching Employee ID, Name to be also Both.  From my screenshot below you can see we have multiple rows with the same Employee ID and Name but the company is showing up with an actual company name rather than Both.

 

So I need to go from this...

 

To this....

 

Any advice or help on this would be greatly appreciated.

 

Thank you.

 

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    MP-iCONN in case you want to do it in the model table with dax, you can add this calculated column:

     

     

    Company Fixed = 
VAR _id = 'Table'[Employee ID]
VAR _companies_count = 
    CALCULATE(
        COUNTROWS('Table'),
        REMOVEFILTERS('Table'),
        'Table'[Employee ID] = _id
    )
VAR _result = 
    IF(
        _companies_count > 1,
        "Both",
        'Table'[Company]
    )
RETURN
    _result

     

     


    Then you can just use this colunn for further analysis in your report and hide the original one

1 Reply

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    MP-iCONN in case you want to do it in the model table with dax, you can add this calculated column:

     

     

    Company Fixed = 
VAR _id = 'Table'[Employee ID]
VAR _companies_count = 
    CALCULATE(
        COUNTROWS('Table'),
        REMOVEFILTERS('Table'),
        'Table'[Employee ID] = _id
    )
VAR _result = 
    IF(
        _companies_count > 1,
        "Both",
        'Table'[Company]
    )
RETURN
    _result

     

     


    Then you can just use this colunn for further analysis in your report and hide the original one