Hi there,
I have a issue with an excel sheet that pulls data in from PowerBI. The data is a list of projects and associated financial information. We then manually add a forecast revenue value each project.
The connection brings through data into columns A to J of a Table. The table has a 7 columns (K to Q), which contains values that are added manually.
When new data is added or removed from the list in PowerBI, the data in columns A to J shift down and up as expected, but the values in columns K to Q stay fixed. This means that the forecast is then not for the correct project.
I am a bit stuck on where to go with it. So, any ideas would be great.
Thanks
Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18623.20116) 64-bit
That's because the manually added columns are by no means linked to the imported data. You could use a technique in Power Query to keep columns K:Q in sync with A:J. Google for "self referencing tables power query" and you'll find many examples of how that works.
You could mimic this automated process by creating a separate table with the data you have in K:Q and add two columns for project number and stage. These two column seem the uniquely identify each row. Now you can use any of Excel's lookup functions or a Power Query merge to 'join' the two tables. However, if your data set is very large and updates frequently, you should go for the first suggested PQ solution.
@riny - Thanks for taking the time to look.
Very much appreciate your support.
