Power Query M - Dinamic Number of Columns

[Anonymous]

Hi!

I have a folder with lots of Excel files with data for each month, the format of those files is almost always the same:

Headers start in line 9 --> this happens in every file.

Two sheets with 12 or 13 columns --> in most files it is 13 but sometimes it has only 12.

Name of columns and data is always structured correctly and in the same way.

Column 12 has garbage data and when the file has Column 13 with data is the same case, later I´ll delete both columns.

The issue is that when I load these Excel files importing from a Folder if every file doesn´t have 13 columns then it won´t allow to load the data to the model.

I´ve tried implementing some M code checking if Table.ColumnCount is less than 13 then add a dummy column Table.AddColumn with null values, but it doesn´t seem to work.

let
    Source = Excel.Workbook(#"Sample File Parameter2", null, true),
    CDF_Sheet = Source{[Item="ABC",Kind="Sheet"]}[Data],
    number_columns = Table.ColumnCount(ABC_Sheet),
    #"Removed Top Rows" = Table.Skip(ABC_Sheet,9),
    #"Fix missing column" = if number_columns < 13 then Table.AddColumn(#"Removed Top Rows", "Column13", null) else #"Removed Top Rows",
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Department"}, {"Column2", "Subject"}, {"Column3", "Shift"}, {"Column4", "Student_LastName"}, {"Column5", "Student_Name"}, {"Column6", "CUF"}, {"Column7", "Note1"}, {"Column8", "Note2"}, {"Column9", "Note3"}, {"Column10", "Final_Note"}, {"Column11", "Presentism"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column12", "Column13"})
in
     #"Removed Columns"

Thanks in advance!

[ElenaN · Accepted solution]

Hello,

I have done very few changes to your code to make it work (highlighted with blue):

let
    Source = Excel.Workbook(#"Sample File Parameter2", null, true),
    ABC_Sheet = Source{[Item="ABC",Kind="Sheet"]}[Data],
    number_columns = Table.ColumnCount(ABC_Sheet),
    #"Removed Top Rows" = Table.Skip(ABC_Sheet,9),
    #"Fix missing column" = if number_columns < 13 then Table.AddColumn(#"Removed Top Rows", "Column13", each null) else #"Removed Top Rows",
    #"Renamed Columns" = Table.RenameColumns( #"Fix missing column",{{"Column1", "Department"}, {"Column2", "Subject"}, {"Column3", "Shift"}, {"Column4", "Student_LastName"}, {"Column5", "Student_Name"}, {"Column6", "CUF"}, {"Column7", "Note1"}, {"Column8", "Note2"}, {"Column9", "Note3"}, {"Column10", "Final_Note"}, {"Column11", "Presentism"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column12", "Column13"})
in
     #"Removed Columns"

Regards,

ElenaN