Add column if not exists

[Anonymous]

Hi community,

once again I have encountered a "challenge":
I have a master file based on several Excel files. These files are all located in one folder and are retrieved via power-query (get data > from file > from > folder). Every month, different departments file their current reports here.

Since last month, it has been decided that the future reports from each department should contain an additional column containing the project type. If these "extended" reports are now stored in the folder and the master file is updated, an error will appear.

My idea now is to add a conditional column in the sample file (in the power editor) in the right place for all files which do not have the column "project type" yet.
So all files would have the same columns.

So far I have difficulties to add this conditional column at the right place. Are there already approaches or maybe even better ideas?

I am glad about every support.

Best regards

[lbendlin]

Look at Table.ColumnCount

https://docs.microsoft.com/en-us/powerquery-m/table-columncount

or Table.ColumnNames

https://docs.microsoft.com/en-us/powerquery-m/table-columnnames

to see if the column is present or not.  Based on that you can then modify your subsequent Table.SelectColumns call.

[parry2k]

@Anonymous how you are appending files together? You can check the missing column name in the file and then add it conditionally something like this.

=if List.Count(List.FindText(Table.ColumnNames( <<your previous step>> ), "ColumnName" ))= 1 then <<previous step>> else Table.AddColumn(<<previous step>>, "ColumnName", each "", type text)

it basically checks if a column name already exists, if yes do nothing otherwise add new column.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k ,

Sorry, I'm still in the training phase of Power Editor.
Is that code you would write in a custom column or in Advanced Editor?

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You may refer to this post:

https://community.powerbi.com/t5/Desktop/Add-a-new-column-to-the-existing-data-set-and-refresh/m-p/116592

Regards,

Lin