Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replace Values with data from another query

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous ,

     

    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?