Power Query - Subtract the earliest date in one column from the record-specific date in another column

1
votes

Every month I download a set of data into a report. That data consists of multiple records and each record has a record specific date as well as having the month end report date on the record's data-row. I have used Power Query to upload all of these month end reports. I want use Power Query to be able to compare the column of record dates with the earliest date in the column of report dates to see if anybody has fiddled any data entry. The query table has the following headings.

Record ID Record Date Report Date

I've tried adding a custom column using the formula = if Record Date < List.Min(Report Date) then "Old" else "New"

this didn't work and I've spent ages trying to get a solution. I've also tried using Groups to get the minimum value, but I lose all of the other columns, which I want to keep. Any help really appreciated.

1
datepowerqueryminimumm

1 Answers

0
votes

You have to refer to the fields in [], so here [Report Date]

To pick a column use Source[Field], so here #"PriorStep"[Report Date]

The List.Min function is not pulling as a number so you cant use <

Insert a Number.From in front of the calculation to convert to number

Same need to add Number.From in front of [Record Date] pulling as a date

Combined code:

#"Added Custom" = Table.AddColumn(#"PriorStep", "Custom", each if Number.From([Record Date])<Number.From(List.Min(#"PriorStep"[Report Date])) then "Old" else "New")