Forum Discussion

adentler's avatar
adentler
Advocate I
11 months ago
Solved

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

  Hello, I am trying to use Power Query to add a column to a table during the transform process. I need to lookup multiple values from Table 1 (Finance Book) in Table 2 (AC_CC Mapping), and return t...
Need Help
  • adentler's avatar
    adentler
    11 months ago

    All - I believe I was able to determine the solution thanks to all of your inputs. I also found the cyclical reference. It was a calculated column post-transform on the Finance Book table.

     

    Ultimately, I was able to use the following code and the column was added as needed:

     

    = Table.AddColumn(#"Changed Type", "Contract Year", (F) => 
  Table.SelectRows( 
    #"ACC_CC Mapping", 
    (C) => C[Start Date] <= F[posting_date] and C[End Date] >= F[posting_date] 
  )
)

     

     

SundarRaj's avatar
SundarRaj
Super User
11 months ago

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,

  • adentler's avatar
    adentler
    Advocate I
    11 months ago

    Thank you! Would you please provide to me the file? I am running into missing token erros here - which I am 100% is all me!

     

    Also - it appears in the code you placed in your comment that you are creating the table as I posted it. Am I correct? If so, I don't believe this will work as the tables already exist and are larger that the sample set I provided. Table 1 has a SQL data source and Table 2 is Excel.