Power Query Add Custom Column with multiple criteria lookup from other table

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

 

 

Hi adentler,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to SundarRaj, MarkLaf, AmiraBedh, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member SundarRaj, MarkLaf, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

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.

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,