Hi @PGG79 , is this what you are looking for? I'll attach the images of the output and M code used. Let me know if I understood your query correctly. Thanks!
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name = "Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Technician", type text}, {"Recording date", type date}, {"Hours recorded", Int64.Type}}),
LookUp = Table.AddColumn(#"Changed Type","LookUpVal",each [Technician] & "-" & Text.From([Recording date])),
Table = Excel.CurrentWorkbook(){[Name = "Table6"]}[Content],
Type = Table.TransformColumnTypes(Table,{{"Technician", type text}, {"Working Schedule", type text}, {"Active since", type date}}),
Position = Table.AddColumn(Type,"HoursRecorded",each List.PositionOf(LookUp[LookUpVal],[Technician] & "-" & Text.From([Active since]))),
#"Recorded Hours" = Table.TransformColumns(Position,{"HoursRecorded", each try LookUp{_}[Hours recorded] otherwise null})
in
#"Recorded Hours"
When thinking of how to model the data you've shared, I would identify your distinct dimensions, pull them out, and construct the applicable bridge tables needed to define their interrletionships. Doing so would most likely result in a constellation model (star schema with multiple facts).
Taking your original data tables, I constructed the below model (Dates is marked as a date table, all top tables are dimensions, bottom tables are facts):
Which is a compoiste of the below three star schemas:
With the above model, something like the below is relatively easy:
Where the only DAX we have to write is for the measure:
Tech Hours = CALCULATE( SUM( TechHours[Hours] ), TechOrgDates, TechCalDates )
If you have any many-to many relationships (e.g., a tech could have two active calendars or be in two positions at the same time), then you should probably stick to a model like this.
If, though, for any given tech and date, you will only have one associated calendar and one associated org/position, then you can simplify the model by moving the foreign keys of your dimensions directly onto your main fact (TechHours). You can then directly relate all your dimensions to the one fact, like so:
With that set up, then you would no even need to write any DAX for the visual example I used before, the implicit sum on TechHours[Hours] works:
Follow up, to show more details of how exactly I transformed your original tables into the model(s) I shared. I did all the transformation/construction in Power Query. It would be possible to do all this in DAX, too, but then you'll have duplicated data loaded into your model.
Here is all the M for the seven tables above.
Calendars
let
Source = WorkingSchedule,
SelectCols = Table.SelectColumns(
Source, {"WorkingSchedule", " MoHrs", "Wk.hrs.", "Hrs/Day", "WkDys", "AnnualHrs"}
),
Distinct = Table.Distinct(SelectCols, {"WorkingSchedule"}),
AddId = Table.AddIndexColumn(Distinct, "ID", 1, 1, Int64.Type),
MoveIdFirst = Table.ReorderColumns(
AddId, {"ID", "WorkingSchedule", " MoHrs", "Wk.hrs.", "Hrs/Day", "WkDys", "AnnualHrs"}
)
in
MoveIdFirst
Dates
let
Source = RecordedHours[Recording Date],
MinDate = List.Min(Source),
MaxDate = List.Max(Source),
Dates = List.Dates(
Date.StartOfYear(MinDate),
Duration.Days(Date.EndOfYear(MaxDate) - Date.StartOfYear(MinDate)) + 1,
#duration(1, 0, 0, 0)
),
ToTable = Table.FromColumns(
{Dates},
Type.AddTableKey(type table [Date = date], {"Date"}, true)
),
AddYear = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), Int64.Type),
AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
AddMonthNo = Table.AddColumn(AddQuarter, "MonthNo", each Date.Month([Date]), Int64.Type),
AddMonth = Table.AddColumn(AddMonthNo, "Month", each Date.MonthName([Date]), type text),
AddYearMonthNo = Table.AddColumn(
AddMonth,
"YearMonthNo",
each [Year] * 100 + [MonthNo],
Int64.Type
),
AddYearMonth = Table.AddColumn(
AddYearMonthNo,
"YearMonth",
each Text.From([Year]) & "-" & Text.Start([Month], 3),
type text
)
in
AddYearMonth
Technicians
let
Source = TechnicianDetails,
SelectTechCols = Table.SelectColumns(Source,{"Technician", "Technician name"}),
DistinctTech = Table.Distinct(SelectTechCols, {"Technician"}),
AddId = Table.AddIndexColumn(DistinctTech, "ID", 1, 1, Int64.Type),
MoveIdFirst = Table.ReorderColumns(AddId,{"ID", "Technician", "Technician name"})
in
MoveIdFirst
OrgPositions
let
Source = TechnicianDetails,
SelectOrgCols = Table.SelectColumns(Source,{"Organization", "JobPosition", "CostArea"}),
Distinct = Table.Distinct(SelectOrgCols),
AddId = Table.AddIndexColumn(Distinct, "ID", 1, 1, Int64.Type),
MoveIdFirst = Table.ReorderColumns(AddId,{"ID", "Organization", "JobPosition", "CostArea"})
in
MoveIdFirst
TechCalDates
let
Source = WorkingSchedule,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
MergeCals = Table.NestedJoin(
MergeTechs, {"WorkingSchedule"},
Calendars, {"WorkingSchedule"},
"Calendars", JoinKind.Inner
),
MaxDate = List.Max(Dates[Date]),
AddDates = Table.AddColumn(
MergeCals,
"Dates",
each List.Dates(
[Start Date],
Duration.Days(List.Min({MaxDate, [End Date]}) - [Start Date]) + 1,
#duration(1, 0, 0, 0)
),
type {date}
),
SelectCols = Table.SelectColumns(AddDates, {"Technicians", "Calendars", "Dates"}),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
ExpandCalID = Table.ExpandTableColumn(ExpandTechID, "Calendars", {"ID"}, {"CalID"}),
ExpandDates = Table.ExpandListColumn(ExpandCalID, "Dates")
in
ExpandDates
TechHours
let
Source = RecordedHours,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
SelectCols = Table.SelectColumns(
MergeTechs,
{"Technicians", "Recording Date", "Hours", "HourType", "project", "Registered on"}
),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"})
in
ExpandTechID
TechOrgDates
let
Source = TechnicianDetails,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
MergeOrgPos = Table.NestedJoin(
MergeTechs,
{"Organization", "JobPosition", "CostArea"},
OrgPositions,
{"Organization", "JobPosition", "CostArea"},
"OrgPositions",
JoinKind.Inner
),
MaxDate = List.Max(Dates[Date]),
AddDates = Table.AddColumn(
MergeOrgPos,
"Dates",
each List.Dates(
[Start Date],
Duration.Days(List.Min({MaxDate, [End Date]}) - [Start Date]) + 1,
#duration(1, 0, 0, 0)
),
type {date}
),
SelectCols = Table.SelectColumns(AddDates, {"Technicians", "OrgPositions", "Dates"}),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
ExpandOrgPosID = Table.ExpandTableColumn(ExpandTechID, "OrgPositions", {"ID"}, {"OrgPosID"}),
ExpandDates = Table.ExpandListColumn(ExpandOrgPosID, "Dates")
in
ExpandDates
If your business rules allow for the simplified model I described (no need to support M:M), then you can disable the load on the two bridge tables, TechCalDates & TechOrgDates, and use the below in TechHours to move the foreign keys over. (Note there are probably more efficient ways to get the FKs here without first building out the bridge tables):
let
Source = RecordedHours,
MergeTechs = Table.NestedJoin(
Source, {"Technician"},
Technicians, {"Technician"},
"Technicians", JoinKind.Inner
),
SelectCols = Table.SelectColumns(
MergeTechs,
{"Technicians", "Recording Date", "Hours", "HourType", "project", "Registered on"}
),
ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
MergeCals = Table.NestedJoin(
ExpandTechID, {"TechID", "Recording Date"},
TechCalDates, {"TechID", "Dates"},
"TechCalDates", JoinKind.Inner
),
ExpandCalID = Table.ExpandTableColumn(MergeCals, "TechCalDates", {"CalID"}, {"CalID"}),
MergeOrgs = Table.NestedJoin(
ExpandCalID, {"TechID", "Recording Date"},
TechOrgDates, {"TechID", "Dates"},
"TechOrgDates", JoinKind.Inner
),
ExpandOrgID = Table.ExpandTableColumn(MergeOrgs, "TechOrgDates", {"OrgPosID"}, {"OrgPosID"})
in
ExpandOrgID
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
