Create Conditional Format in Power Query

[JLambs20]

Hello!

I'm looking to create a conditional column that is based on a different column within a range of cells.  

If the "Status" column contains the word "Finalized" for any of the cells in a range that all have the same value in a separate column (Number), then return "Finalized", if not "Current".  

Here is an example of what I mean

So, for Number 123456, there is one cell in the "Status" column that says "Finalized".  Since that's the case, the new column would say "Finalized" for each of those cells in the row with the same Number.  If the Status column doesn't say "Finalized", then it would say "Current". 

Thank you!

[olgad]

Hi, 

in Power query go to transform-group by

under group by click grouping by number and in the summarized columns pick by max Status (or if you wanna go carefully about then create a conditional before, if finalized then 2 if current then 1) and max by that column plus pick the option All name it All and afterwards expand All with all the columns except for the Number column.

[marcelsmaglhaes]

Hey @JLambs20 !

I dont' know if that is the best alternative, but you can solve the problem duplicating the table. In this duplicate table (Table 2), you can filter Status = Finalized and if there more one stage for the same number Finalized, you can group the table. You just need one distinct number and one finalized status.

After that you can Merge Table and Table 2 in a New Query by the Number column. After the merge process, you expand the Status from the table 2. And you will got the Finalized value for all rows that you need. The null value in Status column you should change to 'Current.'

Makes sense?

Duplicated table wiht Status = Finalized

Merged table with the null value replaced to Current and all 12345 rows with Finalized

[JLambs20]

I didn't end up using this solution but I appreciate the insight! I'll keep this in mind next time I run into a similar issue.