Forum Discussion
Solved
fix query error due to excel numeric stored as text error
How do i fix an error in Power Query Editor to eminates from a common formatting error in excel. excel data is a SQL output and manytimes Excel thinks something is wrong with the excel format but...
Yup! That is where the error is occuring.
I have corrected your "Changed Type" step to treat that field as text. The change is highlighted in green if you scroll to the right.
let Source = Excel.Workbook(File.Contents("K:\Compliance Services\Bryon Brown\My Documents\Data\2017-2018\EndYear\CL33 - Physical Education\Final\CL33 EOY Compliance Report 10172018.xlsx"), null, true), #"Elem Course level_Sheet" = Source{[Item="Elem Course level",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Elem Course level_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Student ID", Int64.Type}, {"School DBN", type text}, {"Term Model", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Grade Level", type any}, {"Official Class", type text}, {"School Year", Int64.Type}, {"Term ID", Int64.Type}, {"Scheduling Method", type text}, {"Course Code", type text}, {"Section ID", Int64.Type}, {"Days per week", Int64.Type}, {"Minutes per week", Int64.Type}, {"MTI minutes per week", Int64.Type}, {"Total PE Minutes (corrected)", Int64.Type}, {"Is the school an MTI all-star? (as of 07/20/18)", type any}, {"Does the course meet requirements?", type text}}) in #"Changed Type"
let
Source = #"Elem Course level",
#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Student ID", "School DBN", "Term Model", "First Name", "Last Name", "Grade Level", "Official Class", "School Year", "Term ID", "Scheduling Method", "Course Code", "Section ID", "Days per week", "Minutes per week", "MTI minutes per week", "Total PE Minutes (corrected)", "Is the school an MTI all-star? (as of 07/20/18)", "Does the course meet requirements?"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Student ID", "School DBN", "Term Model", "First Name", "Last Name", "Grade Level", "Official Class", "School Year", "Term ID", "Scheduling Method", "Course Code", "Section ID", "Days per week", "Minutes per week", "MTI minutes per week", "Total PE Minutes (corrected)", "Is the school an MTI all-star? (as of 07/20/18)", "Does the course meet requirements?"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Official Class", type text}})
in
#"Changed Type"Here you go
Sorry... I should have explained that better. The code you are sending me is from the query that is automatically created to showcase your errors. The actual errors are occuring in your main "Elem Course level" query. Can you open that query and send me the code from the advanced editor within that query?
Here you go.
let Source = Excel.Workbook(File.Contents("K:\Compliance Services\Bryon Brown\My Documents\Data\2017-2018\EndYear\CL33 - Physical Education\Final\CL33 EOY Compliance Report 10172018.xlsx"), null, true), #"Elem Course level_Sheet" = Source{[Item="Elem Course level",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Elem Course level_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Student ID", Int64.Type}, {"School DBN", type text}, {"Term Model", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Grade Level", type any}, {"Official Class", Int64.Type}, {"School Year", Int64.Type}, {"Term ID", Int64.Type}, {"Scheduling Method", type text}, {"Course Code", type text}, {"Section ID", Int64.Type}, {"Days per week", Int64.Type}, {"Minutes per week", Int64.Type}, {"MTI minutes per week", Int64.Type}, {"Total PE Minutes (corrected)", Int64.Type}, {"Is the school an MTI all-star? (as of 07/20/18)", type any}, {"Does the course meet requirements?", type text}}) in #"Changed Type"Yup! That is where the error is occuring.
I have corrected your "Changed Type" step to treat that field as text. The change is highlighted in green if you scroll to the right.
let Source = Excel.Workbook(File.Contents("K:\Compliance Services\Bryon Brown\My Documents\Data\2017-2018\EndYear\CL33 - Physical Education\Final\CL33 EOY Compliance Report 10172018.xlsx"), null, true), #"Elem Course level_Sheet" = Source{[Item="Elem Course level",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Elem Course level_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Student ID", Int64.Type}, {"School DBN", type text}, {"Term Model", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Grade Level", type any}, {"Official Class", type text}, {"School Year", Int64.Type}, {"Term ID", Int64.Type}, {"Scheduling Method", type text}, {"Course Code", type text}, {"Section ID", Int64.Type}, {"Days per week", Int64.Type}, {"Minutes per week", Int64.Type}, {"MTI minutes per week", Int64.Type}, {"Total PE Minutes (corrected)", Int64.Type}, {"Is the school an MTI all-star? (as of 07/20/18)", type any}, {"Does the course meet requirements?", type text}}) in #"Changed Type"Learn something new everyday!!!
Thanks!