Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Fcoatis
Post Patron
Post Patron

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:

 

JOBS.png

 

Table 2 keeps track of historical allocated teams for a specific job:

Original Team.png

I would like Power Query to change Table 1 base on table 2: In this example case June 30th handlig1 task for that job would replace mary Jones by John Smith. Is it possible?

 

Thanks in advance.

 

Link for the model: https://www.dropbox.com/s/i0iju0r60g322bs/Historical.pbix?dl=0

 

1 ACCEPTED SOLUTION

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

 

Left Outer Join - Two Columns.png

After the I expanded the table and just selected the column 

Left Outer Join - Table Expansion and ColumnSelection.png

 

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

Left Outer Join - result.png

 

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

 

2017-08-10_9-25-51.png

 

Here is the result in the report

 

Left Outer Join - result report.png

 

Hope this helps

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

 

Historical.PNG

 

Best Regards

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey you,

 

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

 

Thank you for your time.

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

 

Left Outer Join - Two Columns.png

After the I expanded the table and just selected the column 

Left Outer Join - Table Expansion and ColumnSelection.png

 

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

Left Outer Join - result.png

 

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

 

2017-08-10_9-25-51.png

 

Here is the result in the report

 

Left Outer Join - result report.png

 

Hope this helps

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thank you @TomMartens,

 

My rookie knowledge wasnt aware of the possibility to link 2 columns in the merge step. That was the ace in the hole. next step: Go Live.

 

Best regards.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.