Checking column existence and adding column if not present

[sparkwoodand21]

Hello,

I'm fairly new to PowerQuery but enjoying the process of learning and seeing the obvious benefits it will bring.

I have an immediate situation that is beyond my understanding currently and from reviewing past questions/answers it appears to be new:

I'm running monthly absence reports that 'can' contain up to 40 columns of absence types along with other columns of data. The report only shows those columns containing data each month. So only a selection of the the 40 absence columns available will appear.

I'm using PowerQuery to unpivot the absence report (from a matrix type layout - work shifts on the rows and absence types in columns) into a more normalized structure for loading into a table within a SQL database - which then feeds a dashboard in PowerBI.

So, from:

Date Shift Abs1 Abs2 Abs3 etc.
jan 1 A 3 0 2

to:

Date Shift AbsType AbsValue
Jan 1 A 1 3
Jan 1 A 3 2
etc.

The load to SQL needs to include a column that sums a fixed 25 of the 40 columns to calculate an employed total. Not all the 25 columns will be present each month, but most will.

My thinking was to 'add' the missing columns into PowerQuery for each monthly report so the employed total formula in PowerQuery is static and therefore can always reference the 25 columns it requires. Where a missing column is added it will contain only zero values.

The end of the query filters out all zero values so the missing columns will be excluded from the data loaded to SQL.

I may not be coming at this from the correct angle, but hopefully I've explained the situation sufficiently for understanding.

Any guidance would be appreciated.

Regards,

John

I'm using O365

 

[Matt Allington]

create a new table contains the 25 column names (case sensitive). It can have zero rows in it (ie, just column names). Append the new table to the original. Any missing columns will be added.

 

[sparkwoodand21]

Thank you, Matt

I appreciate your reply as well as the simplicity of your solution.

Apologies for the delay in responding to your message, I've been pulled onto other things and still trying to append the new table as you suggested. As soon as I've managed to complete this task I will update this thread as complete and the solution identified.

Regards,

John

 

[sparkwoodand21]

Hello,

An update finally. The proposed solution by Matt was fantastic and solved my problem.

My final solution had a slight modification as follows:

I created a 'template' table as Matt suggested which contained all the columns I need.

I then combined/appended the data table (each month this has a variety of the template table columns) with the template table and PowerQuery added data where column headings matched and added null where there was no match. The null were then converted to zero to prevent data type issues later on.

Thanks again Matt, spending a few hours setting this up as you suggested will save me time every month trying to map things using the old process. Appreciate you taking the time to consider my problem and provide a quick and workable solution.

Regards,

John