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.