Delete columns if they exist

[trungle]

I'm in the process of setting up a custom function to import multiple CSV files into one dataset. The files are all formatted in the same way, however they don't always contain the same columns. 

The columns are based on the time users spent on different statuses, and each CSV only includes the statuses for which a time was logged. So for example one may have the columns:

Online / Out of Office / Admin

and the next may have

Online / Meeting / Admin

I have a list of all the possible options that may come up so there aren't any unknowns.

Is there a way to edit the TransformColumnTypes and RemoveColumns parts of the query to allow for columns that may not be there - sort of like "if column "online" exists remove it"? 

Thanks in advance

[dedelman_clng · Accepted solution]

What you may actually want is to "unpivot" all of your CSVs before merging them.  When you go to create measures, it could be problematic if you have a "wide" data set with specific columns, rather than a "narrow" dataset with a "TimeType" column and the value.  For example:

Emp Name   Online   Meeting   Offline   
Joe        2        1         1

Emp Name   Online    Away    OutofOffice
Mary       3         1       0.5        

"unpivots" (Transform tab, highlight columns, click "Unpivot Columns") to

EmpName  TimeType   Hours
Joe      Online     2
Joe      Meeting    1
Joe      Offline    1

EmpName   TimeType   Hours
Mary      Online     3
Mary      Away       1
Mary      Offline    0.5

which can easily be merged.

Hope this helps,

David

[dedelman_clng · Accepted solution]

What you may actually want is to "unpivot" all of your CSVs before merging them.  When you go to create measures, it could be problematic if you have a "wide" data set with specific columns, rather than a "narrow" dataset with a "TimeType" column and the value.  For example:

Emp Name   Online   Meeting   Offline   
Joe        2        1         1

Emp Name   Online    Away    OutofOffice
Mary       3         1       0.5        

"unpivots" (Transform tab, highlight columns, click "Unpivot Columns") to

EmpName  TimeType   Hours
Joe      Online     2
Joe      Meeting    1
Joe      Offline    1

EmpName   TimeType   Hours
Mary      Online     3
Mary      Away       1
Mary      Offline    0.5

which can easily be merged.

Hope this helps,

David

[MarcelBeug · Accepted solution]

Alternatively: Table.RemoveColumns has a parameter missingField with possible values (and shortcuts 0,1,2):

MissingField.Error (0)

MissingField.Ignore (1)

MissingField.UseNull (2)

It looks like you could use MissingField.Ignore (or its shortcut: 1).