I'm editing data and I have an entire line of "error" but Power Query Editor won't let me delete it.

[Anonymous]

I have an entire line of "Error" as shown below. I can't determine what's causing the Error as in Excel - my data source - it's just a normal line - line 1653. It's also where Power Query stops showing my data but that's ok because I don't need the data below.

After filtering I'm left with the 60 rows I need but still can't get rid of the error line. When I try to delete it, I get this message:

Anyone have any advice? It would be greatly appreciated.

[AlB · Accepted solution]

Hi @Anonymous 

Try this for the "Energia Elétrica R600 (Euros)" query. I have not looked into the other one but possibly it can be fixed with a similar approach. You'll still have to complete it with further steps I guess (the ones that the error prevented you from adding) See it all at work in the attached file.

let
    Source = Excel.Workbook(File.Contents("d:\Downloads\OneDrive_2021-04-19\power bi\Book1.xlsx"), null, true),
    #"R600-APE_Sheet" = Source{[Item="R600-APE",Kind="Sheet"]}[Data],
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"R600-APE_Sheet", List.Transform(Table.ColumnNames(#"R600-APE_Sheet"), each {_,""})),
    #"Removed Top Rows" = Table.Skip(#"Replaced Errors1",2),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Blank Rows",{"Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Jan_1", "Fev_2", "Mar_3", "Abr_4", "Mai_5", "Jun_6", "Jul_7", "Ago_8", "Set_9", "Out_10", "Nov_11", "Dez_12", "2021_13"}),
    #"Removed Top Rows1" = Table.Skip(#"Removed Columns1",1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Top Rows1", each ([Descrição] = "Estimativa" or [Descrição] = "Forecast" or [Descrição] = "RAID" or [Descrição] = "Real" or [Descrição] = "Year-1")),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Filtered Rows", {{"Descrição", "0"}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Replaced Errors"),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Errors", {"Descrição"}),
    #"Removed Errors2" = Table.RemoveRowsWithErrors(#"Removed Errors1", {"Descrição"})
in
    #"Removed Errors2"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[v-kelly-msft]

Hi @Anonymous ,

Check for any steps that change Data format. These get added automatically. Delete them and then work out what the issue is. Likely a text string in a numeric column, or similar.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AlB]

Hi @Anonymous 

Can you share the excel file and the query you are using to read it, so that we reproduce the issue and look into it?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.