Create column in table if it doesn't exist

[G_Whit-UK]

Hi

I'm working within Power Query to analyse a system output file which lists reconciliation items between two systems.  When the recon doesn't have any exceptions, the output file is simply the column headers.  During the steps within Power BI whereby I merge the various system files together (to build trends, etc) I obviously have to remove the excess file headers - thus on days where there are no exceptions, there are no records on the table.

Later in the process, I pivot the table such that I have a column for each of the systems (2), however on days where there are no breaks, the subsequent steps fail as the pivot is missing one / both of the systems - due to them not having any records.  I therefore want to have some logic introduced after the pivot step to check if a column exists.  If it does, then thats great and the logic doesn't have to do anything.  If the column is missing, then the logic needs to add the missing column (column values can be blank or null).

In order to get the above to work, I was thinking of using an if statement with the Table.HasColumn function, followed by Table.AddColumn where the result was false.  When true, do nothing.  However I've not been able to get this logic to work.

The structure of the pivot table (which is the step just before I'd like to apply the above logic) is as follows (on days where both source files have records):

Record ID	Source System 1 Value	Source System 2 Value
Record 1	1	1.5
Record 2	2	1.9
Record 3	1.7	1.6

On days where one of the source systems didn't report any exceptions, the pivot would look as follows:

Record ID	Source System 1 Value
Record 1	1
Record 2	2
Record 3	2.5

Hence if the output was per the 2nd table above, I'l like to introduce a step whereby the logic creates the missing column.

Thanks in advance for any guidance which you might be able to provide.

[Anonymous · Accepted solution]

Hi @G_Whit-UK ,

List all the required columns in advance and refer to the steps below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNzi9KUTBU0lEKzi8tSk5VCK4sLknNVTBUCEvMKU0FihsqxerAFRrhUWiErNAYn4l65kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    #"Source System List" = {"Source System 1 Value","Source System 2 Value"},
    Custom1 = Table.Group(#"Changed Type",{"Record ID"}, List.Transform(#"Source System List", (y) => { y, (x) => try Table.SelectRows(x, each [Attribute] = y)[Value]{0} otherwise null }))
in
    Custom1

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[lbendlin]

Later in the process, I pivot the table

You usually avoid doing this in Power Query. Let the Power BI visuals do that for you.