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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
artfulmunkeey
Helper I
Helper I

Help with successfully merging tables / lookup?

Hi, I am struggling with the following and wonder if someone can enlighten me as to what i am doing wrong?

 

I have two tables loaded.

 

Table 1 - ResourceDemandTimePhased has (in addition to others) the following columns

 

ResourceDemandTimePhased
ProjectNameResourceNameDateDemand
Project1Dave01/01/2020150
Project1Dave01/02/2020129
Project1Dave01/03/2020100
Project1Dave01/04/2020160
Project1Bob01/01/2020120
Project1Bob01/02/2020140
Project1Bob01/05/2020150
Project2Dave01/01/2020100
Project2Dave01/02/202080
Project2Dave01/03/202060
Project2Dave01/04/202090
Project2Bob01/03/2020100
Project2Bob01/04/2020120
Project2Bob01/05/2020110
Project2Bob01/06/2020100

 

Table 2 - TimeSheets has (in addition to others) the following columns

 

Timesheets
ProjectNameResourceNameDateActual
Project1Dave01/02/202020
Project1Dave01/03/202030
Project1Dave01/04/202050
Project1Dave01/05/202080
Project1Bob01/01/2020100
Project1Bob01/02/2020120
Project1Bob01/05/2020100
Project2Dave01/01/202080
Project2Dave01/02/202060
Project2Dave01/03/202050
Project2Dave01/04/202080
Project5Bob01/03/202090
Project5Bob01/04/2020110
Project5Bob01/05/202080
Project5Bob01/06/202060

 

I wish to create the following outcome. Table 2 doesnt contain nearly as many rows as table 1 (Timesheets are only for a subset of all projects). I do not wish to lose the rows in Table 1 which do not match (Ideally I want to import the Actual hours, matching them to project ID, resource ID and date in Table 1). I have attempted to merge, group and write a lookup function all without success.

 

Desired Output
ProjectNameResourceNameDateDemandActualRemaining
Project1Dave01/01/20201500150
Project1Dave01/02/202012920109
Project1Dave01/03/20201003070
Project1Dave01/04/202016050110
Project1Bob01/01/202012010020
Project1Bob01/02/202014012020
Project1Bob01/05/202015010050
Project2Dave01/01/20201008020
Project2Dave01/02/2020806020
Project2Dave01/03/2020605010
Project2Dave01/04/2020908010
Project2Bob01/03/20201009010
Project2Bob01/04/202012011010
Project2Bob01/05/20201108030
Project2Bob01/06/20201006040

 

Any help would be greatly appreciated, thanks!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@artfulmunkeey - Seems like you could create a composite key in both tables and do a merge in Power Query and that might make things very easy.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@artfulmunkeey - Seems like you could create a composite key in both tables and do a merge in Power Query and that might make things very easy.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks for the fast response and for pointing me down the right track. That worked, however, it has opened another can of worms and I now have a follow-up question if I may..

 

I added a table of resource names and IDs, a separate one for project names and IDs, and a third table as a timeset for every day of the year (dd/mm/yyyy). I've created all the necessary relatioshships and can now produce the following pivot table which can be drilled down to show per resource below each project.

 

 JanFebMar
ProjectNameDemandActualDemandActualDemandActual
Project 1500.0200.0300.0140.0100.0120.0
Project 2700.0950.0750.0900.0750.0150.0
Project 3  50.020.090.090.0
Project 4    100.0110.0
Project 5100.090.090.0100.0100.090.0
Project 6  50.040.080.070.0

 

However, as it is a pivot table with an OData feed i cannot create a calculated field to produce the following desired output. I suspect i need to write a query to do this but do not know where to begin grouping the data by project (and by resources beneath projects) by month, to produce the burn-down of available hours.

 

 JanFebMarCumulativeTOTAL Demand
ProjectNameCumulativeDemandActualRemainingCumulativeDemandActualRemainingCumulativeDemandActualRemaining
Project 1900.0500.0200.0300.0700.0300.0140.0160.0560.0100.0120.0-20.0440.0900.0
Resource 1350.0200.0100.0100.0250.0100.040.060.0210.050.050.00.0160.0350.0
Resource 2550.0300.0100.0200.0450.0200.0100.0100.0350.050.070.0-20.0280.0550.0
Project 22200.0700.0950.0-250.01250.0750.0900.0-150.0350.0750.0150.0600.0200.02200.0
Project 3140.0  0.0140.050.020.030.0120.090.090.00.030.0140.0
Project 4100.0  0.0100.0  0.0100.0100.0110.0-10.0-10.0100.0
Project 5290.0100.090.010.0200.090.0100.0-10.0100.0100.090.010.010.0290.0
Project 6130.0  0.0130.050.040.010.090.080.070.010.020.0130.0

 

Any suggestions on this would be very welcome!

 

Thanks

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors