Error when trying to refresh - was working fine!

[jeags]

Good morning!

I have a turnover dashboard that I update each month with a new headcount file and terminations file. I have been using for months without issue but now suddenly I am getting the following error message when I try to refresh (the query editor is not showing any errors): 

APBI

The syntax for ')' is incorrect. (DAX( MONTH(APBI[Effective])=[LatestMonth]) )). A single value for column 'EE_Location' in table 'APBI' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)).

Files that I use (all excel files):

APBI (Active Power BI): a file I download the first of each month that has all employees

2018 Performance Data: pulls in old performance data for those who are missing it

MissedTerms: a list of employees who terminated within a month and so were not included in one of the monthly APBI files

I have tried the following steps to troubleshoot:

- Verified that all fields in the new files match old files

- Deleted most recent files and tried to refresh to see if there was an issue with the most recent files, get the same error

- Deleted Dax measure below that is the only one including "LatestMonth" - I did this to try to address the first part of the error (The syntax for ')' is incorrect. (DAX( MONTH(APBI[Effective])=[LatestMonth]) )) but not sure why a measure would cause a refresh error. Any ideas what else to look for that this could be referencing?

LatestMonth =

MONTH(MAXX(

KEEPFILTERS(VALUES(APBI[Effective])),

CALCULATE(MAX(APBI[Effective]))

))

- Deleted out steps in the query editor to only include the source

I use a parameter:

let
Source = Excel.Workbook(#"Sample File Parameter1", null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(Sheet1_Sheet,2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee ID", Int64.Type}, {"Preferred Name", type text}, {"Cost Center", type text}, {"Business Title", type text}, {"Position", type any}, {"Job Level", type any}, {"Hire Date", type date}, {"Continuous Service Date", type date}, {"Tenure", type text}, {"Seniority Date", type date}, {"Is Rehire", type text}, {"EE_Location", type text}, {"Time in Position", type text}, {"Gender", type text}, {"Race/Ethnicity", type text}, {"Manager ID", Int64.Type}, {"EE_Manager", type text}, {"Company", type text}, {"Is Manager", type text}, {"Number of Direct Reports", Int64.Type}, {"Number of Direct and Indirect Reports", Int64.Type}, {"Age", Int64.Type}, {"Compensation Grade", type text}, {"Performance Rating 2017", type text}, {"Performance Rating 2018", type text}, {"Performance Rating 2019", type any}, {"Performance Rating 2020", type any}, {"Performance Rating 2021", type any}, {"Latest Manager Scorecard", type text}, {"Manager Scorecard 2019 - Spring", type number}, {"Manager Scorecard 2019 - Fall", type any}, {"Manager Scorecard 2020 - Spring", type any}, {"Manager Scorecard 2020 - Fall", type any}, {"Manager Scorecard 2021 - Spring", type any}, {"Manager Scorecard 2021 - Fall", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each true)
in
#"Filtered Rows"

And an additional query that adds in any missing terminations:

let
Source = Folder.Files("H:\Talent Analytics\TAeP\TP Dashboard Files\Active Power BI"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Active Power BI", each #"Transform File from Active Power BI"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Transform File from Active Power BI"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Active Power BI", Table.ColumnNames(#"Transform File from Active Power BI"(#"Sample File"))),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Expanded Table Column1", "Text Before Delimiter", each Text.BeforeDelimiter([#"Race/Ethnicity"], "("), type text),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", "RaceEthnicity"}}),
#"Inserted Text Before Delimiter1" = Table.AddColumn(#"Renamed Columns2", "Text Before Delimiter", each Text.BeforeDelimiter([Cost Center], " "), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text Before Delimiter1",{{"Text Before Delimiter", "Code"}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Renamed Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Source.Name], "Active PBI ", ".xlsx"), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", type date}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"Text Between Delimiters", "Effective"}}),
#"Inserted Text Before Delimiter2" = Table.AddColumn(#"Renamed Columns3", "Text Before Delimiter", each Text.BeforeDelimiter([Tenure], " "), type text),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Text Before Delimiter2",{{"Text Before Delimiter", "Tenure in Years"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Hire Date", type date}, {"Continuous Service Date", type date}, {"Seniority Date", type date}}),
#"Extracted Date" = Table.TransformColumns(#"Changed Type1",{}),
#"Merged Queries" = Table.NestedJoin(#"Extracted Date",{"Employee ID"},#"2018 Performance",{"Employee ID"},"2018 Performance",JoinKind.LeftOuter),
#"Expanded 2018 Performance" = Table.ExpandTableColumn(#"Merged Queries", "2018 Performance", {"Temporary Performance 2018"}, {"2018 Performance.Temporary Performance 2018"}),
#"Appended Query" = Table.Combine({#"Expanded 2018 Performance", MissedTerms}),
#"Replaced Value" = Table.ReplaceValue(#"Appended Query","Terminate Employee > ","",Replacer.ReplaceText,{"Termination Category"})
in
#"Replaced Value"

I am at my wits end! Seems like no matter what I try I get the same error. Any help would be greatly appreciated!!!!

[mahoneypat]

It seems like you have a duplicate row(s) from something different (or different data) in your latest file.  You can filter out that latest file to confirm everything is still working, but then you need to figure out why there is a duplicate.  You can consider adding a Remove Duplicates step in your query to get rid of it (at column or table level), or you can update your DAX to ignore it.  Hard to propose an exact solution w/o seeing the model or having more details.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jeags]

@mahoneypat thank you for the quick response!

Where are you thinking there is a duplicate row? I have duplicate rows in the missingterms file (one row to count as a hire and one to count as a term). I have tried deleting the most recent files and am still getting the same error. Any other ideas or places to look?

Thanks again!

[jeags]

I have also tried two other things:

- I deleted all relationships in the model to the file APBI - still getting the same error

- Copied the APBI query - that seems to be running okay but if I switch to that one I will have to recreate the calculated columns and 50+ measures on the copy

Does that help to narrow in on what the issue might be at all?

[mahoneypat]

From what you described and the error message you saw, it looks like an issue with one of your calculated columns on the APBI table.  Do any of them use the [Latest Month] measure?  Start there if so.  In any case, you can figure out which column by "coding out" each one by one.  Turn the expression to code by putting // in front of it or /* ...*/ around it (your code is the ellipsis) if multiple lines.  Before the comment, just have each return 1 or something  ( my column = 1 //CALCULATE(...   ).  Try to refresh when each column is coded out to see which one is causing your problem.

Regards,

Pat

[jeags]

@mahoneypat thanks for the idea! I tried using the mycolumn = 1 //CALCULATE on all of my calculated columns in the APBI table and am still getting the same error when I try to refresh. The only thing using LatestMonth is the measure LatestMonth and is not referred to by other measures or columns. Double checked using the method described here: https://exceleratorbi.com.au/measure-dependencies-power-bi/

Any other ideas to troubleshoot? 

[mahoneypat]

Other than a simple close and reopen of your file, no more ideas at the moment.

Regards,

Pat