Forum Discussion

rdevlin's avatar
rdevlin
Regular Visitor
6 years ago
Solved

Load table with calculated formula intact

I have, for example, a table with two columns in excel. The first is a text field and the second is a calculated column:

     A                 B

1   Name         Calculated Column

2    Mr X           =A2

 

I then query this table via power query and load the results back to another worksheet. The resulting table has two text columns.

    A                   B
1 Name           Calculated Column
2 Mr X              Mr X

 

Can you clarify if it is possible to actually return the formula as before ( ie so the formula  = A2 is in the new table's cell B2 replicating the original calculated column) and not return the result of the calculated column as hard coded text. If yes, how?

 

Thanks 

 

Need Help
  • rdevlin's avatar
    rdevlin
    6 years ago

    Thanks for your help on this but formula.text still doesnt bring the column in as a calculated column.

     

    Looking into it all further today I have found a workaround solution - if I  (i) initially just query the first column, (ii)  load it back into the workbook as a new table, (iii) then insert a new column to the right in the new table and (iv) then add the formula in the new column it creates a calculated column which refreshes everytime the original table data is updated and the power query refreshed!

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    You could try the Formulatext function in Excel before you load it to Power Query. Then you are getting the actual formulas.
    • rdevlin's avatar
      rdevlin
      Regular Visitor
      6 years ago

      Thanks for your help on this but formula.text still doesnt bring the column in as a calculated column.

       

      Looking into it all further today I have found a workaround solution - if I  (i) initially just query the first column, (ii)  load it back into the workbook as a new table, (iii) then insert a new column to the right in the new table and (iv) then add the formula in the new column it creates a calculated column which refreshes everytime the original table data is updated and the power query refreshed!