Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bbrown44
Advocate II
Advocate II

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 snapshotPowerBI snapshot

 

 

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

Please advise,

~Bryon

1 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"

 

View solution in original post

11 REPLIES 11
drewlewis15
Solution Specialist
Solution Specialist

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?

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"

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

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?

 

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

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!

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"

tables.JPG

 

 

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.