Adding calculated columns to tables returned from Power Query

[andydtaylor]

Is there a way to make this work?

Scenario: Table of variable height is returned from Power Query to Excel workbook. For visible audit purposes ONLY some calculations are added to this table before it is sucked back into power query. This is because my manager freaks at an if statement in Power Query.....

Usual excel functionality is for a formula to be extended down when new table rows are added. However this does not appear to be the case for tables returned from Power Query. Is there a way to make this work? VBA is a no-go.

Thanks,

Andy

 

[VBA Geek]

You could add this column directly with Power Query? If it is an easy calculation then you can do it straight from the User Interface, don't let your Boss be too bossy

 

[andydtaylor]

I'm afraid I'm going to have to go with the misuse of Power Query. At least at this time. I have found a solution. If you go to the table properties in Design you can check a "preserve column sort/filter/layout" option. If you *then and only then" add your extra worksheet calculated column the formulas will extend/contract as power query data is refreshed. This gives me an easy path to a visual audit trail which steps back the high-tech of Power Query enough that I hope it will prove acceptable.