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. 

     

     

Gabriel_Walkman's avatar
Gabriel_Walkman
Icon for Continued Contributor rankContinued Contributor
5 years ago

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?