Power Query Add Column with formula

[barrywhitelaw]

Hi

I'm aware you can't add, in Power Query, a formula that when closed/loaded/refreshed to excel runs automatically, but is there a way to add a column in Power Query with anything in it that is ignored by Power Query but forwards to Excel as text?

Eg, I need to add this formula, but PQ I think is seeing some of it and trying to action it. I just need the whole cells ignored and not run:
=IF(L2-"","",IF(COUNT(L$1:L$1,L2)>=1,"",L2))

I've tried adding a custom column and using = "=IF(L2-"","",IF(COUNT(L$1:L$1,L2)>=1,"",L2))" but all I get is an error. I can add plain text fine, but anything else errors.

Again I'm aware this likely will need dragged down the column, but that's a simple step.

thanks

[AliGW]

No, it can't be done.

You might be able to adapt this technique to add a formula column: https://exceleratorbi.com.au/self-re...s-power-query/

[josephteh]

Try creating the column directly in the table loaded by PQ.

[AliGW]

You can't do that - it's not possible to add a formula to an empty column in a PQ-generated table as it will be removed each time you refresh. You'd have to add the formula back again manually every time.

@BarryWhitelaw - thanks for the rep. Glad to have helped.

[josephteh]

Please see attached workbook.

[AliGW]

OK - I misunderstood your suggestion - sorry. I thought you were saying that you were adding the column via the query.

I suspect that the OP's requirement was more complex, though, which is why they went with my suggestion.

[josephteh]

Haha.. no worries..