Forum Discussion
Solved
Remove last column / Import from folder
I´m trying to import excel files from a folder. It works fine, if the excel files are simular. If a column is missing i get an error and i don´t know how to fix it. It would be great to get help on t...
You can add a step to remove the last column.
let Quelle = Excel.Workbook(Parameter1, null, false), BWA_sheet = Quelle{[Item="BWA",Kind="Sheet"]}[Data], FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or Text.Trim(_) <> "")), #"Entfernte untere Zeilen" = Table.RemoveLastN(BWA_sheet, each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false), #"Entfernte oberste Zeilen" = Table.Skip(#"Entfernte untere Zeilen", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false), #"Entfernte letzte Spalte" = Table.RemoveColumns(#"Entfernte oberste Zeilen", {List.Last(Table.ColumnNames(#"Entfernte oberste Zeilen"))}) in #"Entfernte letzte Spalte"
Looks good! And i imported the file with missing columns - but the TOTAL (always the last) need to be deleted.
You can add a step to remove the last column.
let
Quelle = Excel.Workbook(Parameter1, null, false),
BWA_sheet = Quelle{[Item="BWA",Kind="Sheet"]}[Data],
FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or Text.Trim(_) <> "")),
#"Entfernte untere Zeilen" = Table.RemoveLastN(BWA_sheet, each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
#"Entfernte oberste Zeilen" = Table.Skip(#"Entfernte untere Zeilen", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
#"Entfernte letzte Spalte" = Table.RemoveColumns(#"Entfernte oberste Zeilen", {List.Last(Table.ColumnNames(#"Entfernte oberste Zeilen"))})
in
#"Entfernte letzte Spalte"