Error Messages

[Anonymous]

Hello All,

I have 3 tables in my data model, all have the same source.

I have it 3 times for security reasons.

For some reason I keep getting this error message that there are 72 errors on only 1 of the tables and not the other 2, even though the source is the exact same excel sheet.

When I click to view errors, I don't see any errors.

Any help would be appreciated

@edhans 

[edhans]

A lot of time when I see errors like this but you cannot find them in the PQ window itself it is some sort of data mismatch with how the column is typed (date, whole number, etc.) vs what formulas or even DAX is expecting when it comes in.

It can be tedious to find sometimes.

[edhans]

by the way, in order to really help, we'd need a PBIX file to look at, plus the source files as PQ will just show an error if the source isn't there.

[Anonymous]

PBIX file:

https://www.dropbox.com/s/hzbagqtar9fl2ky/8%20in%208%20Reporting.pbix?dl=0

Source Excel File:

https://www.dropbox.com/s/90z2sm5xuyrv7so/PayrollTracker.xls?dl=0

[edhans]

Yup. Data type issue. You are trying somewhere to convert an email address into a number.

[Anonymous]

So do I need to edit the source file in some way to prevent this from happening?

[edhans]

Edit what source? Not sure what you are referring to. The email column should be text, not number. As for XLS vs XLSX, you need your source system to export in XLS or you need to convert yourself. THere are macros available to automate that kind of thing or Power Automate scripts if data is stored on a SharePoint site.

[edhans]

Your problem is in the Payroll Tracker Entity Level:

= Table.TransformColumnTypes(#"Promoted Headers",{{"Entity", type text}, {"Business Unit", type text}, {"DEPTID", Int64.Type}, {"DEPTNAME", type text}, {"VP ID", Int64.Type}, {"VP Name", type text}, {"Email", type text}, {"DIRECTOR_EMPLID", Int64.Type}, {"DIRECTOR_NAME", type text}, {"DIR_EMAIL_ADDR", type text}, {"MGR ID", Int64.Type}, {"Manager", type text}, {"MGR Email", type text}, {"Employee ID", Int64.Type}, {"NAME", type text}, {"Jobcode", type text}, {"Jobtitle", type text}, {"HOURLY_RT", type number}, {"COMP_FREQUENCY", type text}, {"FLSA_STATUS", type text}, {"FULL_PART_TIME", type text}, {"FTE", type number}, {"Goal Hour", type number}, {"Add_Email_1", Int64.Type}, {"Add_Email_2", type text}, {"AE_EMAIL1", type text}, {"AE_EMAIL2", type text}, {"AE_EMAIL3", type text}, {"AE_EMAIL4", type text}, {"AE_EMAIL5", type text}, {"AE_EMAIL6", type text}, {"AE_EMAIL7", type text}, {"Dustin_Email", type text}, {"Trallyn_Email", type text}, {"Hours Used", type number}, {"Hours Used Earnings", Currency.Type}, {"Hours Used Capped", type number}, {"Scheduled Hours", Int64.Type}, {"Scheduled Hours Capped", Int64.Type}, {"Scheduled Hours Earnings", Currency.Type}, {"LWOP Earnings", Currency.Type}, {"Vacation Accrued", type number}, {"Holiday Accrued", type number}, {"Personal Holiday Accrued", type number}, {"Hours to Goal", type number}, {"Hours Worked", type number}})

Your Add_Email_1 column is being converted to an integer. That is the error.

[Anonymous]

I see what you mean, just fixed it! Works great now

[edhans]

Excellent @Anonymous . Glad we tracked it down.

[Anonymous]

Sorry I meant do I need to edit the excel sheet in some way? Or this is a problem in need to fix in Power BI?

[edhans]

By the way @Anonymous , you should edit your post above and remove those links. There is confidential data in here.

[edhans]

One final note (assuming this is resolved) I could not for the life of me get this to work with the April 2020 desktop update. The XLS file format was causing a big problem. I converted to XLSX and it worked fine. You should consider doing the same. It might be I don't have the Access 2010 distributable installed, which is required for some XLS stuff. And it could be MS isn't testing as much with XLS.

There are known bugs with XLS files and Power BI and MS has no intention to fix them.

[Anonymous]

You are right my bad, I though these were my test files

[Anonymous]

Right, it is just so odd that it is only erroring out on one of the tables and not the other two

[amitchandak]

@Anonymous , Open Edit Query/ Data Transformation from home Tab. There you will see error in yellow

[Anonymous]

Here it is, am I missing something?

[camargos88]

Hi @Anonymous ,

Check the values before convert them.

The columns:

Scheduled Hours Earnings,

Hours Used Earnings,

LWOP Earnings

have the character $, first remove it and change the datatype to numerical type.

Also the column Add_Email_1 is as numeric, it has text values.

[Anonymous]

You mean make the changes in the excel file before I refresh the data?