Replace Values with data from another query

[Anonymous]

Dear All,

I successfully transformed all the query’s which should be included in my report but unfortunately stuck on the last step.

If you look at the pictures below, I have a two Data tables, first one named Budgeted data which consist of Budgeted data and forecasted Actual data which will be spend on some projects by months (for the whole year). Other one is Actual data which consist of Actual hours spend on that Project and its updated on the monthly basis.

Thing for what I need your help and like hear you solution is how to set up (probably M code or some other way) query that will automatically overwrite data from the "Actual hours" column of "FTE Budgeted data" with data from "Actual hours" column of Table "FTE Actual data" based on Project Manager, Project code and Date, so practically to update that "FTE Budgeted data" Table with data from "FTE Actual Data" table. Also, since data in "FTE Actual data" Table is updated on monthly basis it have a data for only those months which already passed (like from January-November), data for December should stay the same as it is in "FTE Budgeted Table" since this is some kind of prediction when we try to look the data for the whole year, same will be for the future when we get Actual hours data for December, Actual hours from "FTE Budgeted Data" should be overwritten by new data, but data for January 2021 should stay as it is in the "FTE Budgeted Data".

Could you find some solution for this, probably it will be simple for you but I’m relatively new in this and would really appreciate your help.

Link to Pictures: https://imgur.com/a/aNDmqyY

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Your requirement is that overwritten "Actual hours in "FTE Budgeted data" by "Actual hours in "FTE Actual data" which has the same value in Project Manager, Project code and Date.

I think Merge is a good function in Power Query.

My sample is as below, merge two tables by Project Manager, Project code and Date.(Use shift+Left Click to select multiple columns)

Then Expand Actual Hours in "FTE_Actual Data". It will show "null", due to the values in 2021/1 haven't been updated.

Then build a conditional column as below.(Let Actual Hours show values in "FTE_Budget data" if FTE_Actual Data.Actual Hours is null. show itself, it has value.)

Finally remove other Acutal Hours columns and rename Custom as "Actual Hours"

You can download the pbix file from this link: Replace Values with data from another query

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

HI @v-rzhou-msft ,

Thanks for your solution, I was also thinking in the same way as you.

This is good solution and should work, but what unfortunatly I dont have are the same Projects in both Tables.

For example when I try to Merge those two tables, result is for exp. 853 rows of 1100 match one another.

Thing is that when I get that Budgeted Table from other Dept. they only include Ongoing Projects data (remove finished Projects), and in the Actual Hours there are all hours spent for that Month even for those who ended but had some hours spent in that month.

Thing that I did (not the best solution) is : duplicate Budgeted hours table, remove all the columns which are not necessarily, but leave that Actual hours column. Remove all the data before 1/12/2020 and then append it to Actual hours table which have the data until December . This worked but the thing is that i will need to delete additional month in Helper table every time new data in FTE Actual hours comes in.

I have Project Lookup table invoked (with all Project, even the ones which ended) as connection but what would be your solution there?

[Gabriel_Walkman]

I'm quite new myself, so take this with a grain of salt.

I'd do this either via power query or dax.

Power query: merge all three tables as one, and create a new calculated column so that

- if there's data in actual, use it. If not

- use data in forecast

But I'm not sure what you should use to merge the tables. Can one project number have different managers?

The imgur images are not very readable due to quality issues. Can you post them here?