0
votes

I'm trying to code in Power Query Editor a step to replace all "Error" with null or 0. The "Error" fields appear in the "WorkingDays" column. The source is a simple Excel file on my desktop. My code is the following:

let
    Source = Excel.Workbook(File.Contents("C:\Users\60069970\Desktop\Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Release Date", type date}, {"Closed Date", type date}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "WorkingDays", each Query1([Release Date], [Closed Date])),
    #"Custom1" = Table.ReplaceErrorValues(Sheet1_Sheet,{{"WorkingDays",0}})
in
    #"Custom1"

I keep getting the same error indicating that WorkingDays column isn't found on the table:

Expression.Error: The column 'WorkingDays' of the table wasn't found. Details: WorkingDays

Re-checking my code I don't what's the issue. Perhaps I'm not specifying the table or column correctly.

Thanks!

Regards.

1

1 Answers

0
votes

You specify wrong table in last step, should be:

#"Custom1" = Table.ReplaceErrorValues(#"Invoked Custom Function",{{"WorkingDays",0}})