Get & Transform vs Conditional Format

user_2872922 0
Viewed 136

I am trying to use conditional formatting on the output of a Get & Transform query. (Office 365/Excel 2016 32 bit; Windows 10 Pro 64 bit).

However, when the query is refreshed, instead of the Conditional Formatting merely being extended/contracted to affect the resized column, it gets fubar'd as shown below.

I know I can obtain the desired output in a variety of ways, including VBA, and I do not need assistance in developing other methods.

Am I overlooking something either in the UI, or perhaps some method I could implement in the Advanced Editor to accomplish the normal extension of CF one would see if one just added a line to the table?

To reproduce the problem:

Base Data

enter image description here

Use Get & Transform to unpivot columns 2-4, and remove the Attribute column

enter image description here

Conditional format to hide all except the first unique entry in column A1

enter image description here

-->

enter image description here

Add another row to the original data table:

enter image description here

Update the query.

Note that the display and the CF "applies to" are messed up, and an extra CF condition is added

enter image description here

Is there anything I can do within the Query, or within Excel, to keep this from happening? Or do I have to edit the conditional formatting (vba or manual)? (Or use a different solution to develop the output).

I have tried defining the applies to range using the structured reference, or using a dynamic name range. but that did not work. The applies to gets changed to an absolute reference, and develops the same changes as noted above when the Query is refreshed.

get-transform conditional-formatting excel
1 Answers

This seems to work for me (but I literally just learned you could even do this with data by reading your post, so thank you!).

Set your conditional formatting calculation to

=COUNTIF($A$2:$A2,$A2)>1

and apply it to =$A$2:$A$4.

As an added bonus, every time I refreshed my data after updating the original table, my Applies to under the conditional formatting rule expanded the range down.

user_64783630
Related