Help with tables and power query

[Pheonex723]

I've run into this problem with a couple different projects and want to know what's the best way to accomplish. I want to pull data from various online sources and generate a table, I can do this much. The problem being if I then add formulas directly into the cells in a new column they either erase or get overwritten by the current calculated value, I understand why this is happening. I want the formulas to stay "live" and update when data changes, not have to refresh constantly. I know I can put them in a separate table but I don't always know how many rows power query is going to pull or when I do refresh the data because more items were added to the sources. I do not have access to the source data. Do I do the oversize the table trick and hope the source never exceeds the size I pick? Hard code formulas into pq and refresh constantly? Pull data into a raw table and manually copy and create an input table every time I do need to refresh?(how I've been doing it) Please advise and help.

 

[Monty]

Hello Phonex

It seems like you're dealing with a challenge related to maintaining live formulas alongside dynamically changing data in Excel. One approach you can consider is using Excel Tables in conjunction with Power Query. This method allows you to maintain dynamic formulas and handle varying amounts of data.

Here's a step-by-step guide:

Import Data with Power Query:

Use Power Query to import your data into Excel. Ensure you load it as a table.
If you don't know the exact number of rows in advance, that's okay. Power Query can handle dynamic ranges.
Create an Excel Table:

Convert the range of data you loaded with Power Query into an Excel Table.
Select any cell within the data range.
Press Ctrl + T or go to the "Insert" tab and select "Table."
Add Calculated Columns:

Now, you can add your formulas in additional columns within this Excel Table.
These formulas will automatically adjust as the table size changes.
Use Structured References:

Reference columns using structured references (e.g., Table1[Column1]). This ensures that your formulas adapt to changes in the table size.
Power Query for Dynamic Range:

When using Power Query, it dynamically adjusts to the changing range. You don't need to manually refresh or redefine the table size.
Handle Expanding Data:

If your source data expands, Power Query can be configured to handle this dynamically. It's designed to adjust to changes in the data structure.
By following these steps, you should be able to create a setup where your formulas remain live and adapt to changes in the data pulled by Power Query.

Remember to test and adjust your solution based on the specific characteristics of your data and workflow.

 

[Pheonex723]

Ok... I have no idea what I did then. I basically tried that but when I hit refresh my manually created columns would empty. I tried self referencing but that would overwrite the formula with the calculated value. Thank you so much for your help.