fix query error due to excel numeric stored as text error

[Bbrown44]

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 there is nothing wrong.  see excel example below.

Variable "Official Class" comes up as a error because excel wants to view the data as a numeric value, however there are text included and so I ignore the error in excel.

However, when i upload it to Power BI the variable come up as an error.  See snapshot below.

PowerBI snapshot

How do I resolve this error and make the Power BI query treat this data as text?

Please advise,

~Bryon

[drewlewis15]

My guess would be that the error is occuring during the initial "change type" step that Power Query likes to add for you at the beginning.  Can you send the code from your advanced editor?

[Bbrown44]

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"

[drewlewis15]

It looks like the Source is pointing to the "Elem Course level" query.  Can you send the code for that query?

[Bbrown44]

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

[drewlewis15]

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?

[Bbrown44]

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"

[drewlewis15 · Accepted solution]

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"

[Bbrown44]

Learn something new everyday!!!

Thanks!

[Bbrown44]

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"

[Bbrown44]

[Bbrown44]

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"