Conditional column to apply only on first instance

[anna-lee]

Hi,

I'm having difficultly creating a conditional column to only apply on the first instance and populate "null" afterwards. I have an Index column with no duplicates, but I'm currently still recieving two "Yes" when I only want one. My goal is to remove all the rows below the first instance of "Total" in my first column, but some tables mention "Total" more than once. I do not have an ID column so grouping is not an option. My formula is:

= Table.AddColumn(#"Filtered Top Row", "Bottom Row Conditional", each if [Column1] = "Total" and [Index] = List.Min({List.Single({[Index]})}) then "Yes" else "null")

[watkinnc · Accepted solution]

You can make a duplicate query, add an index, find the first instance of your value, and then use that index number as the parameter for Table.SplitAt. Then you can transform on your bottom table, and then Union (Table.Combine) your tables back together.

--Nate