Forum Discussion
Helper IIIupdate row column value based on another column value in another row
Hi all,
I would like to filter all rows that have the "Match" column value equal to 1 and update accordingly all the other rows that have the same unique id " _uuid" with the value equal to 1.
Example in the below table, we have 2 rows having the same _uuid; however,only one row has the "Match" column value equal to 1. Hence, I want to update the other row column "Match" value to 1 as well.
| _uuid | CaseNo | caseno1 | section1/location/district_s | Match |
| 240a1dd3-fc69-47a9-8a88-783606b261b4 | LEB-19C82143 | Marjaayoun | 0 | |
| 240a1dd3-fc69-47a9-8a88-783606b261b4 | 909-14C07402 | 909-14C07402 | Marjaayoun | 1 |
Hi RaffiKouzoudj,
You can do this on the Power Query level.
With such a dataset, right click on the Match column and select Group By.
Then, in the settings, select _uuid as the column you will group by and add the Max aggregation for the Match column. Why this way? If the _uuid has 1 in this column, it will return max 1, if it has 0, it will return 0.
Also add the column Other, which will contain all the other records.
The next step is to click on the Expand icon and select the columns - in your case, select these 3. Click ok.
All entries have been updated to match what has a specific _uuid.
_______________
If I helped, please accept the solution and give kudos! 😀RaffiKouzoudj
You can create the following calculated column:New Match column = IF(CALCULATE(SUM([Match]),ALLEXCEPT('Table','Table'[_uuid]))>=1, 1, 0)
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
RaffiKouzoudj , Create a new column like
Match new = if(isblank(countx(filter(table, [_uuid] =ealier[_uuid] && [Match] =1),[_uuid])),0,1)
RaffiKouzoudjHi Amit Thank you for the reply. However, i am getting an error message: "too many arguments were passed to the filter function. the maximum argument count for filter funtion is 2.
- lkalawski
Resident Rockstar
Hi RaffiKouzoudj,
You can do this on the Power Query level.
With such a dataset, right click on the Match column and select Group By.
Then, in the settings, select _uuid as the column you will group by and add the Max aggregation for the Match column. Why this way? If the _uuid has 1 in this column, it will return max 1, if it has 0, it will return 0.
Also add the column Other, which will contain all the other records.
The next step is to click on the Expand icon and select the columns - in your case, select these 3. Click ok.
All entries have been updated to match what has a specific _uuid.
_______________
If I helped, please accept the solution and give kudos! 😀
RaffiKouzoudj
You can create the following calculated column:New Match column = IF(CALCULATE(SUM([Match]),ALLEXCEPT('Table','Table'[_uuid]))>=1, 1, 0)
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
