Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
9 years ago
Solved

Power Query Assistance

Hello community,   I need help with this:    Table 1 registers tasks of people for a specific job, but it registers only the current team:     Table 2 keeps track of historical allocated...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    I'm not sure if I understood your data model 100% but I used two columns for the Left Outer Join: fullJOB and Task

    The changed file is here

     

    After the I expanded the table and just selected the column 

     

    Rows in the base Table "Jobs" have not been duplicated ...

     

    After that I created a custom column Team", but as far as I understood your requirement it would also been sufficient to take Initial Team column

     

     

    Here is the result in the report

     

     

    Hope this helps

     

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

in the QueryEditor

  1. Mark your first table
  2. On the Home ribbon select "Merge Queries" (at the far right)
  3. Select the second table
  4. Mark the columns in both tables that can be considered as key columns
  5. Select the join type
  6. Expand the table, just select initial team
  7. create a new custom column, get the value from initial team
  8. remove all unwanted columns

Hope this gets your started

Regards

  • Fcoatis's avatar
    Fcoatis
    Post Patron
    9 years ago

    Thank you TomMartens,

     

    That was my initial approach. But the table expansion gets records duplicated. After that result I dont know how to replace the task team and delete the duplicates I dont need. Check the link of the model if you will.

     

     

    Best Regards

    • TomMartens's avatar
      TomMartens
      Super User
      9 years ago

      Hey

      what columns did you use for the join, were you expecting duplicate rows

      yes: why

      no: maybe a column is missing during the join 

       

      I will have a closer look at your model tomorrow.

       

      Cheers

      • Fcoatis's avatar
        Fcoatis
        Post Patron
        9 years ago

        Hey you,

         

        I used fullJOB for the join and I didnt expect duplicates.

         

        Thank you for your time.