Hi @adentler, another PQ solution. Let me know if this is what you were looking for. I have indeed used List.PositionOf here. I think one important parameter you missed here in List.PositionOf was Occurence.All
It would give you all the rows associated with that cost center and post that, adding a condition custom column would give you boolean values of what you desire.
I'll attach the code below. Let me know if you need the file for better understanding of the steps. Thanks
let
// Finance Book Table
FinanceBook = #table(
{"Document Number", "Cost Center", "Posting Date"},
{{12345, 1234, #date(2024, 8, 5)}, {67890, 5678, #date(2025, 7, 2)}}
),
// AC_CC Mapping Table
AC_CC_Mapping = #table(
{"Account Code", "Contract Year", "Start Date", "End Date", "Cost Center"},
{
{"123AB", "Year 1", #date(2024, 7, 1), #date(2025, 6, 30), 1234},
{"123AB", "Year 1", #date(2024, 7, 1), #date(2025, 6, 30), 5678},
{"223AB", "Year 2", #date(2025, 7, 1), #date(2026, 6, 30), 5678}
}
),
AddColumn = Table.AddColumn(
FinanceBook,
"Values",
each List.Transform(
List.PositionOf(AC_CC_Mapping[Cost Center], [Cost Center], Occurrence.All),
each AC_CC_Mapping{_}
)
),
List = Table.ExpandListColumn(AddColumn, "Values"),
Record = Table.ExpandRecordColumn(
List,
"Values",
{"Account Code", "Contract Year", "Start Date", "End Date", "Cost Center"},
{"Account Code", "Contract Year", "Start Date", "End Date", "AC_CC_Mapping.Cost Center"}
),
#"Added Custom" = Table.AddColumn(
Record,
"Condition",
each [Posting Date] >= [Start Date] and [Posting Date] <= [End Date]
),
#"Filtered Rows" = Table.RemoveColumns(
Table.SelectRows(#"Added Custom", each ([Condition] = true)),
"Condition"
)
in
#"Filtered Rows"
Regards,
You'll usually get better performance with a join in this kind of scenario. In case you somehow added a dependency on 'Finance Book' within 'AC_CC Mapping', I've written this as a third query, which should avoid circular ref errors.
// Assuming that we'll rename original 'Finance Book' as 'Finance Book_init' and disable load
// We can then rename this new query as 'Finance Book' to load into model
let
// Get dates from start - end on map and expand
Map = #"AC_CC Mapping",
Map_AddDates = Table.AddColumn(
Map, "Dates",
each List.Dates(
[Start Date],
Int64.From( [End Date] - [Start Date] ) + 1,
#duration(1,0,0,0)
),
type {date}
),
Map_ExpandDates = Table.ExpandListColumn(Map_AddDates, "Dates"),
// join map and expand Contract Year on above Map_ExpandDates
Book = #"Finance Book_init",
Book_JoinMap = Table.NestedJoin(
Book, {"Cost Center", "Posting Date"},
Map_ExpandDates, {"Cost Center", "Dates"},
"join", JoinKind.LeftOuter
),
Book_ExpandContractYear = Table.ExpandTableColumn(
Book_JoinMap, "join",
{"Contract Year"}, {"Contract Year"}
)
in
Book_ExpandContractYear
----------
As an aside, a circular reference error from the AmiraBedh's solution seems odd. If there was a circular reference issue at the time of your original post, I believe it would have taken priority over your type mismatch error (as in, you would not have seen the error you posted, only the circular ref error). Did you change something in your queries (that would have introduced a Finance Book -> AC_CC Mapping dependency) between your original post and when you tried solutions? Or did you paste the code (which references FinanceBook) inside the FinanceBook query? If a query references itself, that would also cause the error.
Hello !
Thank you for posting on Microsoft Fabric community.
The AND only works on logical values but I can see that in your code you are mixing a value ofCost Center with boolean. I shared the file with the solution.
= let
FB = Table.TransformColumnTypes(FinanceBook, {{"Cost Center", type text}, {"Posting Date", type date}}),
MAP = Table.TransformColumnTypes(#"AC_CC Mapping",{{"Cost Center", type text}, {"Start Date", type date}, {"End Date", type date}}),
WithCY = Table.AddColumn(
FB,
"Contract Year",
(row as record) as nullable text =>
let
matches = Table.SelectRows(
MAP,
each [Cost Center] = row[Cost Center]
and row[Posting Date] >= [Start Date]
and row[Posting Date] <= [End Date]
),
best = if Table.IsEmpty(matches)
then null
else Table.Sort(matches, {{"Start Date", Order.Descending}}){0}[Contract Year]
in
best,
type nullable text
)
in
WithCY
Helpful resources
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
