Column name Change Error

[hasharma19]

Hello All,

Dealing with column name change error in Power Query.
I have linked a excel file to Power BI and to get the columns name i have to removed few top rows.

Now whenever i am removing the first linked file from source folder and trying to referesh the data set, an error poping up stating that that" The Column 'Harish Sharma'of the table wasn't found(since aftre removing top row and using first row as header the name has filtered out).

Can you please suggest the get rid of this issue. Since Its an old file which is not required to be display(data) have to detele. But with new file linking it is giving me error. The 2nd highlighted row  of table is the final header of the table.

[Ritaf1983]

Hi @hasharma19 
You need to modify your steps and "tell" to engine not to take deleted items.
just delete them from here:

For more information please refer to this tutorial:

https://www.youtube.com/watch?v=bPOQCDluVs8

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[hasharma19]

@Ritaf1983 Tried with help of suggest video but seems missing somthing or apply wrong steps.
still getting the same error while apply full refresh

[hasharma19]

@Ritaf1983 -Can you please check below codes.
The column which i  am taking about is not deleted from source file,

I did some transformation and the column"Harish Sharma" is filtered out only. 

let
Source = Folder.Files("C:\Users\hasharma\OneDrive - DPDHL\Desktop\DG Audit Reports"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Reviewer Name:", type text}, {"Harish Sharma", type any}, {"Column4", type text}, {"Site reviewed", type text}, {"Column6", type text}, {" ", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", type text}, {"Column20", type any}, {"Column21", type any}, {"Column22", type text}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type any}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type any}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type any}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}, {"Column96", type any}, {"Column97", type any}, {"Column98", type any}, {"Column99", type any}, {"Column100", type any}, {"Column101", type any}, {"Column102", type any}, {"Column103", type any}, {"Column104", type any}, {"Column105", type any}, {"Column106", type any}, {"Column107", type any}, {"Column108", type any}, {"Column109", type any}, {"Column110", type any}, {"Column111", type any}, {"Column112", type any}, {"Column113", type any}, {"Column114", type any}, {"Column115", type any}, {"Column116", type any}, {"Column117", type any}, {"Column118", type any}, {"Column119", type any}, {"Column120", type any}, {"Column121", type any}, {"Column122", type any}, {"Column123", type any}, {"Column124", type any}, {"Column125", type any}, {"Column126", type any}, {"Column127", type any}, {"Column128", type any}, {"Column129", type any}, {"Column130", type any}, {"Column131", type any}, {"Column132", type any}, {"Column133", type any}, {"Column134", type any}, {"Column135", type any}, {"Column136", type any}, {"Column137", type any}, {"Column138", type any}, {"Column139", type any}, {"Column140", type any}, {"Column141", type any}, {"Column142", type any}, {"Column143", type any}, {"Column144", type any}, {"Column145", type any}, {"Column146", type any}, {"Column147", type any}, {"Column148", type any}, {"Column149", type any}, {"Column150", type any}, {"Column151", type any}, {"Column152", type any}, {"Column153", type any}, {"Column154", type any}, {"Column155", type any}, {"Column156", type any}, {"Column157", type any}, {"Column158", type any}, {"Column159", type any}, {"Column160", type any}, {"Column161", type any}, {"Column162", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "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", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162"}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",7),
#"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Review Question#", type text}, {"Section", type text}, {"Assessment question", type text}, {"local translation", type text}, {"Business Rule", type text}, {"Reviewers Guidance", type text}, {"Severity", type text}, {"Compliance#(lf)(Y, N, N/A)", type text}, {"Compliant#(lf) Level 1 =6#(lf)Level 2 = 2", type any}, {"Non-Compliant Level 1 = -12#(lf)Level 2 = -2", type text}, {"N/A#(lf)Level 1 = -6#(lf)Level 2 = -2 ", type any}, {"Comments", type text}, {"Site Reviewed", type text}, {"Review Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([#"Review Question#"] <> "Review Question#")),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Compliant#(lf) Level 1 =6#(lf)Level 2 = 2", Int64.Type}, {"Non-Compliant Level 1 = -12#(lf)Level 2 = -2", Int64.Type}, {"N/A#(lf)Level 1 = -6#(lf)Level 2 = -2 ", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,0,Replacer.ReplaceValue,{"N/A#(lf)Level 1 = -6#(lf)Level 2 = -2 "}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Non-Compliant Level 1 = -12#(lf)Level 2 = -2"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"Compliant#(lf) Level 1 =6#(lf)Level 2 = 2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value2",{{"Compliant#(lf) Level 1 =6#(lf)Level 2 = 2", Int64.Type}, {"Non-Compliant Level 1 = -12#(lf)Level 2 = -2", Int64.Type}, {"N/A#(lf)Level 1 = -6#(lf)Level 2 = -2 ", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type3", each ([#"Review Question#"] <> null and [#"Review Question#"] <> "Total Restricted Commodities audit and compliancy rating")),
#"Inserted Year" = Table.AddColumn(#"Filtered Rows1", "Year", each Date.Year([Review Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Review Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Month", "Custom", each if[Month]<6 then "H1" else "H2"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "H1_H2"}})
in
#"Renamed Columns"