corregir el error de consulta debido a Excel numérico almacenado como error de texto

¿Cómo soluciono un error en Power Query Editor que surge de un error de formato común en Excel?

Los datos de Excel son una salida SQL y muchas veces Excel piensa que algo está mal con el formato de Excel, pero no hay nada de malo. vea el ejemplo de Excel a continuación.

La variable «Clase oficial» aparece como un error porque Excel quiere ver los datos como un valor numérico, sin embargo, hay texto incluido, por lo que ignoro el error en Excel.

Sin embargo, cuando lo subo a Power BI, la variable aparece como un error. Vea la instantánea a continuación.

Instantánea de PowerBI

¿Cómo resuelvo este error y hago que la consulta de Power BI trate estos datos como texto?

Por favor avise,

~ Bryon

¡Sip! Ahí es donde está ocurriendo el error.

He corregido el paso «Tipo modificado» para tratar ese campo como texto. El cambio se destaca en verde si se desplaza hacia la derecha.

let
    Source = Excel.Workbook(File.Contents("K:Compliance ServicesBryon BrownMy DocumentsData2017-2018EndYearCL33 - Physical EducationFinalCL33 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"

Supongo que el error se produce durante el paso inicial de «tipo de cambio» que a Power Query le gusta agregar al principio. ¿Puedes enviar el código desde tu editor avanzado?

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"

Parece que la fuente apunta a la consulta «Nivel de curso de Elem». ¿Puede enviar el código para esa consulta?

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"

Aqui tienes

Lo siento … debería haberlo explicado mejor. El código que me está enviando es de la consulta que se crea automáticamente para mostrar sus errores. Los errores reales están ocurriendo en su consulta principal de «Nivel de curso elem». ¿Puede abrir esa consulta y enviarme el código del editor avanzado dentro de esa consulta?

Aqui tienes.

let
    Source = Excel.Workbook(File.Contents("K:Compliance ServicesBryon BrownMy DocumentsData2017-2018EndYearCL33 - Physical EducationFinalCL33 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"

¡Sip! Ahí es donde está ocurriendo el error.

He corregido el paso «Tipo modificado» para tratar ese campo como texto. El cambio se destaca en verde si se desplaza hacia la derecha.

let
    Source = Excel.Workbook(File.Contents("K:Compliance ServicesBryon BrownMy DocumentsData2017-2018EndYearCL33 - Physical EducationFinalCL33 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"

¡¡¡Aprenda algo nuevo cada día!!!

¡Gracias!

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"

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"

Entradas relacionadas

Deja un comentario Cancelar respuesta