How to put a break point/tracing errors during query execution

[gpiero]

I'am facing a strange error message during the stage showed in the image below.

The warning indicates  that a specific column is not in the table. But I think this message is misleading.

Is there a way to do a sort of debug at this stage? It would help to understand which line generates the warning.

Can you suggest some method?

regards

[Maddy99]

did you aready use the power Query editor?

sometime the error there is more clear. 

try to refresh the tables there and see if a clear error apears. 

[gpiero]

@Maddy99

Yes, I did,

but in my opinion the message is not coerent to the point where it is showed

I am not able to get the link between the message and the last line before "in" statement.

For this reason I believe that message is misleading

let
    Source = Csv.Document(File.Contents("D:\OneDrive - Mycompany\Lavori\01.20_-_POWER BI DATASOURCE\KPI WH & SH\COOIS.txt"),[Delimiter="|", Columns=30, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Order     ", "Order"}, {"Material  ", "Material"}, {"     WBS Element", "WBS Element"}, {"Description                             ", "Description"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Order", type text}, {"Material", type text}, {"Description", type text}, {"WBS Element", type text}, {"Target qty", Int64.Type}, {"Bsc start ", type date}, {"Basic fin.", type date}, {"Sched.Fin.", type date}, {"System Status", type text}, {"Changed on", type date}, {"Changed by", Int64.Type}, {"Text", type text}, {"Sales Ord.", type text}, {"SO Item", type text}, {"Icon", type text}, {"Order Type", type text}, {"MRP ctrlr", type text}, {"Pr.Superv.", type text}, {"Plant", type text}, {"Unit", type text}, {"Type", type text}, {"Long text", type text}, {"Created on", type date}, {"Entered by", Int64.Type}, {"CollectOrd", type text}, {"SuperOrder", type text}, {"Reserv.No.", Int64.Type}, {"Request ID", type text}, {"", type text}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type1",{"Order", "Material", "Description", "WBS Element", "Target qty", "Bsc start ", "Basic fin.", "Sched.Fin.", "System Status", "Changed on", "Changed by", "Text", "Sales Ord.", "SO Item", "Icon", "Order Type", "MRP ctrlr", "Pr.Superv.", "Plant", "Unit", "Type", "Long text", "Created on", "Entered by", "CollectOrd", "SuperOrder", "Reserv.No.", "Request ID"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns1",{{"Order", Text.Trim, type text}, {"Material", Text.Trim, type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Trimmed Text", "TECO_OK", each if Text.Contains([#"System Status"], "TECO") then "TECO" else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"TECO_OK", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Order", "Material", "Description", "WBS Element", "Target qty", "Bsc start ", "Basic fin.", "Sched.Fin.", "TECO_OK","System Status", "Changed on", "Changed by", "Text", "Sales Ord.", "SO Item", "Icon", "Order Type", "MRP ctrlr", "Pr.Superv.", "Plant", "Unit", "Type", "Long text", "Created on", "Entered by", "CollectOrd", "SuperOrder", "Reserv.No.", "Request ID"})
in
    #"Reordered Columns"

[Maddy99]

in query editor you see the steps on de applied window.

you have 10 steps. if it goes wrong in sep 7, then that error appears in step 8, 9 and 10.

system status was used first in the add column step.

  #"Added Conditional Column" = Table.AddColumn(#"Trimmed Text", "TECO_OK", each if Text.Contains([#"System Status"], "TECO") then "TECO" else null),

i think the # is wrong. it should be

#"Added Conditional Column" = Table.AddColumn(#"Trimmed Text", "TECO_OK", each if Text.Contains([System Status], "TECO") then "TECO" else null),

[gpiero]

@Maddy99

I've tried but it does not work

[gpiero]

@Maddy99

I think the sintax is correct. In fact in another query it works in a proper way

#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "CheckGroupMtrl1", 
                  each if Text.Trim([#"COOIS.FatherGroup"]) <> null
                          and [#"Material  "] <> null
                          and Text.Trim([#"COOIS.FatherGroup"]) = [#"Material  "]
                  then 1

[Maddy99 · Accepted solution]

then  dont know what it could be from my computer.

i hope some one else can help. sorry

[gpiero]

@Maddy99

Sorry for my delay in closing this post.

It is not a matter of wrong sintax.

I had a similar problem in a new PBI report. Then we discovered that a certain field of of our ERP contains a variable number of strings.

It is highlithed by "*" at the end of the field. The I added in the query Split By Characther, creating 25 new columns.

Issue solved.

Regards