CODEWITHSUNDEEP
excelpivot-tablepowerquery
Kzryzstof
Kzryzstof

Reputation: 8382

Power Query - The field 'xxx' of the record wasn't found

I want to add a new column to a source of data.

The new column's value is based on the data of the current row (To Resolved date and From Activated date) as well as data from another query (Calendar). Here is the a screenshot of the source data I am working on:

Source data

I am trying to get the number of rows of the other source (Calendar) that are within the range [From Activated date, To Resolved date]. For now, I have this formula for the new column (without the counting yet): 

= Table.SelectRows(Calendar, each _[Date] >= [From activated date] and _[Date] <= [To Resolved date])

However, it does not work because Power Query does not find the columns From Activated date and To Resolved date in the Calendar query.

Expression.Error: The field 'From activated date' of the record wasn't found.

Details: Date=2017-01-01 Year=2017 MonthNumber=1 MonthName=January Day=1 Weekday=1 WorkingDays=0

Question

How can I get the current values of From Activated date and To Resolved date for the current row ?

Upvotes: 0

Views: 8084

Answers (1)

Olly
Olly

Reputation: 7891

Add another query as a function - in this case, I named it 'CalendarRows':

(StartDate, EndDate) =>
let
    Source = Table.RowCount(Table.SelectRows(Calendar, each [Date] >= StartDate and [Date] <= EndDate))
in
    Source

Now add a column to your data table, referencing this function:

= Table.AddColumn(#"Previous Step", "Calendar Rows", each CalendarRows([From activated date], [To Resolved date]))

Upvotes: 2

Related Questions