Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |