cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bbrown44 Regular Visitor
Regular Visitor

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

excel snapshot.JPG

 

 

 

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.JPGPowerBI snapshot

 

 

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

Please advise,

~Bryon

1 ACCEPTED SOLUTION

Accepted Solutions
drewlewis15 Member
Member

Re: fix query error due to excel numeric stored as text error

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"

 

11 REPLIES 11
drewlewis15 Member
Member

Re: fix query error due to excel numeric stored as text error

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 Regular Visitor
Regular Visitor

Re: fix query error due to excel numeric stored as text error

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 Member
Member

Re: fix query error due to excel numeric stored as text error

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

Bbrown44 Regular Visitor
Regular Visitor

Re: fix query error due to excel numeric stored as text error

tables.JPG

 

 

Bbrown44 Regular Visitor
Regular Visitor

Re: fix query error due to excel numeric stored as text error

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 Regular Visitor
Regular Visitor

Re: fix query error due to excel numeric stored as text error

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 Regular Visitor
Regular Visitor

Re: fix query error due to excel numeric stored as text error

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

Highlighted
drewlewis15 Member
Member

Re: fix query error due to excel numeric stored as text error

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?

 

2018-10-18_16-36-34.png

Bbrown44 Regular Visitor
Regular Visitor

Re: fix query error due to excel numeric stored as text error

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"