Error (row index 1)

[PGAT]

Hi all, for a while now, the PBI has been working well for me until today. I suspect I may have deleted a step, and now I cannot figure out what went wrong.  I look up the error but I don't understand. Does this line in red mean give you any clue that can help me? I am afraid it is not possible for me to share the other queries as there are many tables appended together.  Appreciate the advice from a kind soul.  Thanks. 

Sincerely,

Patrick

let
Source = #"Table20191307 (Appended)",
#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Job Ty", "Svc Ty", "Job Mo", "Booker", "Cty", "Partner", "Ship Org", "Ship Dest", "Company", "WT", "Unit", "Relo", "Relo Cty", "Transferee Name", "Delvy Date", "Start Date", "End Date", "Shpt ETD", "Shpt ETA", "Coordinator", "POE", "No of Inv.", "Inv.AMT", "Year (TEXT)", "R-Date", "YYYY", "TYPE", "Country", "Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Job Ty", "Svc Ty", "Job Mo", "Booker", "Cty", "Partner", "Ship Org", "Ship Dest", "Company", "WT", "Unit", "Relo", "Relo Cty", "Transferee Name", "Delvy Date", "Start Date", "End Date", "Shpt ETD", "Shpt ETA", "Coordinator", "POE", "No of Inv.", "Inv.AMT", "Year (TEXT)", "R-Date", "YYYY", "TYPE", "Country", "Year"})
in
#"Reordered Columns"

[amitchandak]

@PGAT , Have you created an index column and used it in some other column and deleted it , reverse the deleted step(remove it), or add it again

[PGAT]

Hi Amitchandak, thanks, oh no... I am really not sure. All the steps look so right.  Oh man...

[parry2k]

@PGAT update your line as below, it is a missing last parameter:

#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1, 1),

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[PGAT]

Hi there, thanks, what do you mean by missing last parameter?  

[parry2k]

#"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1, 1)

You are missing last parameter ,1 in above M code.

[PGAT]

Hi, thanks again,  the  previous display was from the error page, and it relates to this table below. Where can i go to update the line "Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1, 1)? sorry, do I go to error page to update? 

let
Source = #"Table20191307 (OA)",
#"Appended Query" = Table.Combine({Source, #"Table20191307 (DA)", #"Table20191307 (CA)", #"Table20191307 (AGT-KCDCA)"}),
#"Added Conditional Column" = Table.AddColumn(#"Appended Query", "Country", each if [TYPE] = "OS" then [Ship Org] else if [TYPE] = "DS" then [Ship Dest] else [Ship Dest]),
#"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column", "Merged", each Text.Combine({[Year], "-1-1"}), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged", type date}}),
#"Extracted Year" = Table.TransformColumns(#"Changed Type",{{"Merged", Date.Year, Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Year", "Year (TEXT)"}, {"Merged", "Year"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","DO NOT USE -THE MI","MOVERS INTERNATION",Replacer.ReplaceValue,{"Booker", "Partner"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","DO NOT USE - THE M","MOVERS INTERNATION",Replacer.ReplaceValue,{"Booker", "Partner"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","DO NOT USE-THE MI","MOVERS INTERNATION",Replacer.ReplaceText,{"Booker", "Partner"})
in
#"Replaced Value2"

[parry2k]

@PGAT you have to look at the queries that use addindexcolumn function, this particular query is not using that, what other tables you are getting error in Power Query, you have to check all those, without seeing the full picture, it is very hard to provide the appropriate solution. When you find the table which has the error, just update the syntax as provided.

[PGAT]

Thank you, Sir!  Is there a possibility that i will not be able to find this? As in, if i have deleted it by mistake. I am not sure. I really hope to be able find that missing piece so that I can update it.  The error shown relates to only this "Table20191307" but yet, I cannot locate addindexcolumn in the query.  I understand it is difficult as you do not have view to all my queries. I really appreciate every possible step you are advising.  Thanks. 

[PGAT]

hi there, this issue is now fixed.   The issue was oddly with a column where i had the year in TEXT.  e.g. 2020.  I then made a formula to combine TEXT with "-1-1" where I can then convert to date column, and to extract YEAR.   This was working fine for me in this 1.5 year until now.  Once I removed all the M code below, and kept the year as TEXT. I had no other errors.  I still can't figure out why these steps below will trigger the issue.  I got to know these issues as I was working to start from scratch again. Then it hit errors at this step.