CODEWITHSUNDEEP
powerquery
user3159004
user3159004

Reputation: 5

Compare List from 'embedded' table with main column

(see screenshot) i have my main table and in a column I have an embedded table. I need to compare Date column in embedded table with a condition AND test with against column in main table... I thought I could use List.Transform to bring a list of the Dates (which I can) but if I try to do a List.Transform on this function (that returned the list) i get an error

We cannot convert a value of type Function to type Type. Details: Value=[Function] Type=[Type]

= Table.AddColumn(#"Added Custom1", "Custom", each List.Transform( List.Transform( [MD_call_Data][Date], each _ )), each Date.Month(_) = Date.Month(#date(2023,2,1) and _ < [Min. Blood_Collection_Date] ))

I also tried columns as List and it errors List.Transform([MD_call_Data][Date], each Date.Month(_) = Date.Month(#date(2023,2,1) and _ < [Min. Blood_Collection_Date])))

pl see screenshot for column Headers. how can i bring in the list and compare against a column and another condition?

(https://i.sstatic.net/eEmhs.png)

Upvotes: 0

Views: 30

Answers (1)

horseyride
horseyride

Reputation: 21393

Is this what you are shooting for?

  • you need Number.From() wrapped around dates to compare them

  • you need to use the let xxx in yyyy format to pull in a field before this List.Transform

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each let test=[Min. Blood_Collection_Date] in List.Transform([MD_call_Data][Date], each Date.Month(_)=2 and Number.From(_)<=Number.From(test)))

Upvotes: 0

Related Questions