Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |