cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mriccardi Frequent Visitor
Frequent Visitor

Power Query M - Dinamic Number of Columns

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ElenaN Member
Member

Re: Power Query M - Dinamic Number of Columns

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

 

1 REPLY 1
ElenaN Member
Member

Re: Power Query M - Dinamic Number of Columns

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

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 42 members 1,008 guests
Please welcome our newest community members: