Conditional Formatting and Power Query

[juanand]

Hi there,

I am struggling with Conditional Formatting and Power Query.

Short question is ... is there a way to prevent conditional formatting from shifting in a Power Query-based table when adding/removing/reordering columns in PowerQuery?

I can add or remove additional columns to table after loaded data from Power Query, and conditional formatting formulas recalculate applicable cells and stays fine.

However, if I reorder/add/remove columns to source data in PowerQuery and reload data to Excel table, the conditional formatting goes mad and formulas apply to the unintended column.

For example, if formula paints red cells based on formula “=AND($P13″United States”;$O13<″200”), applying to columns “Country” ($P) and “Sales” ($O), if then add/remove/reorder columns in PowerQuery source and reload data, coloring applies to i.e. “Product” (now in $P) and “Population” (now in $O), instead of “Country” (now in $Q) and “Sales” now in $B), breaking the whole thing.

I tried =AND(MyTable[Country]”United States”;MyTable[Sales]<”200”) but such formula is not accepted in the conditional formatting.

Thanks!

[#2]

Try if this helps:
Right-click the table in question and choose Table, "External data properties...". Set the table to "Overwrite existing cells with new data, clear unused cells".

[#3]

@Jan Karel Pieterse 

Thanks, that option did it actually did not solve the problem, but was really close (I guess the intent :) ) !!

It works for my scenario to tick the "Preserve column sort/filter/layout".

That allows moving columns around in Excel itself, respecting the conditional formatting formulas, or hiding columns as needed, instead of touching the PowerQuery results and breaking the column sorting and conditional formatting of resulting data table.

Thanks a lot!
 

[#4]

@juanand, If I ever met you I would give you a big hug! Have no idea the struggle I've been going through on this issue. 

This worked!