Forum Discussion
Delete columns if they exist
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
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
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).
2 Replies
- dedelman_clng
Community Champion
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
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).