Apr 28 2020 05:18 AM
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!
Apr 28 2020 06:15 AM
May 05 2020 03:45 AM
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!
Aug 20 2021 02:27 PM
@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!