Forum Discussion
Column name Change Error
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
Ritaf1983 Tried with help of suggest video but seems missing somthing or apply wrong steps.
still getting the same error while apply full refresh
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"