Remove last column / Import from folder

[SvenJ]

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 this.

This is the template file:

 

If a file from the folder has missing columns like this i get an error - also the last column (total) need to be deleted.

 

Can someone help me on this?

[SvenJ]

Great! Thanks for your help. Last question. I want to have the percentage different between the years. For example, i select a filter years 2019 & 2021 - i want to have the difference between the values of each month. If i select 2018 & 2019, the difference between this years. If i select 3 years, the difference of each year. 2018 (difference in %) 2019 (difference in %) 2021

[AlexisOlson]

This belongs as a separate post (probably in DAX rather than Power Query).

[SvenJ]

Hi @AlexisOlson 

here is my code from table "Jahresübersicht"

let
    Quelle = Folder.Files(Datenpfad & "Jahresübersicht"),
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(Quelle, each [Attributes]?[Hidden]? <> true),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte Tabellenspalte1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Geänderter Typ",{"Column15"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"Column1", "Zeile"}, {"Column2", "Bezeichnung"}, {"Column3", "Januar"}, {"Column4", "Februar"}, {"Column5", "März"}, {"Column6", "Arpil"}, {"Column7", "Mai"}, {"Column8", "Juni"}, {"Column9", "Juli"}, {"Column10", "August"}, {"Column11", "September"}, {"Column12", "Oktober"}, {"Column13", "November"}, {"Column14", "Dezember"}}),
    #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Umbenannte Spalten", {"Source.Name", "Zeile", "Bezeichnung"}, "Attribut", "Wert"),
    #"Umbenannte Spalten2" = Table.RenameColumns(#"Entpivotierte andere Spalten",{{"Attribut", "Monat"}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Umbenannte Spalten2", each ([Bezeichnung] <> "Bezeichnung")),
    #"Eingefügter Textbereich" = Table.AddColumn(#"Gefilterte Zeilen", "Textbereich", each Text.Middle([Source.Name], 15, 4), type text),
    #"Umbenannte Spalten3" = Table.RenameColumns(#"Eingefügter Textbereich",{{"Textbereich", "Jahr"}}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Umbenannte Spalten3",{"Source.Name", "Zeile", "Bezeichnung", "Wert", "Monat", "Jahr"}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",{{"Wert", type number}, {"Jahr", Int64.Type}}),
    #"Ersetzter Wert" = Table.ReplaceValue(#"Geänderter Typ1","Arpil","April",Replacer.ReplaceText,{"Monat"}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Ersetzter Wert",{{"Zeile", Int64.Type}})
in
    #"Geänderter Typ2"

Not sure, if you also need this code - it´s from "transform example file"

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),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Entfernte oberste Zeilen",{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}})
in
    #"Geänderter Typ"

As mentioned above, some files don´t have all columns - the last "total" column is not needed in the scenario.

[AlexisOlson]

Thanks. This helps a lot.

The first thing I would try is to simply remove the last step of the transformation function so that it looks like this:

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)
in
    #"Entfernte oberste Zeilen"

This way there aren't any explicit column names until you've expanded 

[SvenJ]

Looks good! And i imported the file with missing columns - but the TOTAL (always the last) need to be deleted.

[AlexisOlson · Accepted solution]

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"

[SvenJ]

M code is the code of the advanced editor?

[AlexisOlson]

Yes.

[AlexisOlson]

I'm guessing you have a step that's transforming column data types that refers to all the columns by name. There are a few possible workarounds.

1. Unpivot all except the first column before setting the column type.
2. Make the code more dynamic using Table.ColumnNames instead of the actual names.
3. Remove that step from the transformation function and set the column type after combining all the files.

[SvenJ]

Thanks for your answer. Can you give me an example? I don't know how to fix the problem.

[AlexisOlson]

If you share your M code, I can point you to which parts to modify.