Data type error in Excel Power Query
I am using Power Query in Excel and am getting data type errors. I have 34 source tables that I have appended into one query. The data from one table in the appended query gives a data type error on one column, saying that it can't convert the data to a number, even though it is set as text. The query for that table and column is set to text and gives the same error. The source table column is formatted as text. Even new data entered into the source table gets the same error. Please help! There are no special symbols or numbers in the source data.
I have attached some of the Excel sheets I'm working with. The 2020 Monthly Haul Sheet is one of the source files (August_2020 is the table with problems), and the MTR Report - NEW is where the query is. I can send the other two source sheets, if needed.
I have attached some of the Excel sheets I'm working with. The 2020 Monthly Haul Sheet is one of the source files (August_2020 is the table with problems), and the MTR Report - NEW is where the query is. I can send the other two source sheets, if needed.
ASKER
2020 Monthly Haul Sheet.xlsxMTR Report - NEW.xlsx
Sorry. I was sure I had attached them.
The reason I think it's that particular column in that table is because when I open the query for that table, it shows errors in the column, and it doesn't show it in the other tables. The data is importing correctly from the other tables. I don't know much about Power Query, just enough to be dangerous.
Sorry. I was sure I had attached them.
The reason I think it's that particular column in that table is because when I open the query for that table, it shows errors in the column, and it doesn't show it in the other tables. The data is importing correctly from the other tables. I don't know much about Power Query, just enough to be dangerous.
What column is the problem in?
Looks like the query is not finding the source file for June and August. If you changed the file name or location from the last time the query was run it will error. Either need to save erroneous file in same location and with same name as previous file, or change the location to the new file.
Oops.. My mistake I don't imagine you are getting that error.
Okay, let me start over. So the 34 tables are all brought into Power Query editor as separate queries, right?
If I look at the M code for the months that the query worked (April, May, July) have one more line of code than does August. I am thinking this may be the problem. Now, the solution may be to simply copy that line of code and add it to the August query and refresh and see if this works. This is assuming the data fields are the same every month. Do you want to give this a try?
Which query in NEW is the problem? Aug_2020?
Okay, I have attached three months of Power Query code for June, July and August for you to see. My guess is there is a problem with data type in your queries. If you look at #"Changed Type" & #"Changed Type1" for each of the months you will see inconsistencies. This could lead to data type errors when trying to append these queries together.
My guess is the original queries for each of these months (#"Changed Type") attempts to determine the data type upon load of the source data. But because sometimes data coming in differently from source the query can make different decisions for a field's data type. This is the first problem.
Then someone is trying to change the original data types with #"Changed Type1", but may not be covering all the differences. Part of the solution should be (for all queries) to delete the data type step #"Changed Type", and make all the changes to your data types one time manually. Then those changes should be replicated in all the queries.
EE Power Query.docx
My guess is the original queries for each of these months (#"Changed Type") attempts to determine the data type upon load of the source data. But because sometimes data coming in differently from source the query can make different decisions for a field's data type. This is the first problem.
Then someone is trying to change the original data types with #"Changed Type1", but may not be covering all the differences. Part of the solution should be (for all queries) to delete the data type step #"Changed Type", and make all the changes to your data types one time manually. Then those changes should be replicated in all the queries.
EE Power Query.docx
ASKER
I'm sorry for the slow reply. The error is in the Load Type column.
So, if I understand, I need to remove all of the Changed Type steps from all queries, and then only change it in the appended one?
So, if I understand, I need to remove all of the Changed Type steps from all queries, and then only change it in the appended one?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I consolidated four months from your data. See how it looks..EE-PowerQueryTest.xlsx
ASKER
It's still not showing the load type for August. I am going to try creating a new test file like you suggested and see if I can get it to work. I will not be able to respond for a few days. I will be on vacation, away from technology, until Wednesday.
No problem. Are these monthly tables going to be in the same workbook? Separate workbooks?
ASKER
They are all going to go into one workbook. I am summarizing all of the data and comparing by year and month.
I took your advice and started from scratch. I created queries for each of the source tables, deleted the bottom row (Totals) from each sheet, appended them, and then changed the data type for each column. The errors are gone. Thank you for your help. I think I just over complicated something that should have been simple.
I took your advice and started from scratch. I created queries for each of the source tables, deleted the bottom row (Totals) from each sheet, appended them, and then changed the data type for each column. The errors are gone. Thank you for your help. I think I just over complicated something that should have been simple.
My hints: