Removal of consecutive Yes

[Sunil M P]

QUERY : There should not be 2 consecutive "YES". If so, then the 2nd "YES" should be replaced with "NO". Please find below the sample Input data and desired Output, to be achieved using POWER QUERY.[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]INPUT[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]

 

[billszysz]

Hi
Try this code:

let
Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
AddedIndex = Table.AddIndexColumn(Source, "Indeks", 0, 1),
AddCol1 = Table.AddColumn(AddedIndex, "Custom", each if [INPUT] = "NO" then [Indeks] else null),
FillDown1 = Table.FillDown(AddCol1,{"Custom"}),
RepVal = Table.ReplaceValue(FillDown1,null,1,Replacer.ReplaceValue,{"Custom"}),
AddCol2 = Table.AddColumn(RepVal, "Custom.1", each if [INPUT] = "YES" then [Indeks] - [Custom] else 1),
AddCol3 = Table.AddColumn(AddCol2, "Custom.2", each if Number.IsEven([Custom.1]) then "NO" else [INPUT]),
RemOtCol = Table.SelectColumns(AddCol3,{"Custom.2"}),
ChNameCol = Table.RenameColumns(RemOtCol,{{"Custom.2", "Output"}})
in
ChNameCol

 

[Sunil M P]

Thanks a lot billszysz