Search code examples
excelexcel-formuladaxpowerquery

Data from Power Query not matching once referenced in Excel

I have a Power Query connection that is loaded to the local data model so that the data can be referenced in Excel formulas. When I reference that data in a formula , "=MAX(PayrollDates)", the data returned does not match the data shown when editing the query. Excel Output

Data returned from formula is "45688" or "1/31/2025" where the table in the data model and the PQ is "45345" or "2/23/2024", depending on data type set in PQ.

enter image description here

enter image description here

This is the initial table: enter image description here

This is the M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="PayrollDates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Process Date", type date}, {"Pay Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "CurrentPayperiod", each if [Start Date] < DateTime.Date(DateTime.LocalNow()) and DateTime.Date(DateTime.LocalNow()) < [Process Date] and DateTime.Date(DateTime.LocalNow()) > [End Date] then true else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([CurrentPayperiod] = true)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Start Date", Int64.Type}}),
    #"Start Date" = #"Changed Type1"{0}[Start Date]
in
    #"Start Date"

I have tried converting the final column in PQ to text, date, and whole number. It always comes back with the incorrect date once reference from the Excel formula.

Solution

  • What I found was that I could reference the final result of the PQ by loading the PQ to a DM, adding a measure to the DM (MIN in this case as I changed the M-Code), then reference the measure using the CUBEVALUE function in Excel.