PQ FindText exact matching columnname

Hi all

I get data from several environments, and they might not all have the same columns. In order to be able to "align" those, so I can use the same Power Query on them, I add some columns, if they do not already exist.

I do this with the following code:

Please Login or Register  to view this content.

This works well, and it will create a column called "Colors", if it does not exist and if it already exists, it does nothing.

The problem is that FindText does not search for exact text. So if I have 2 columns, respectively called "Fruits" and "BlueFruits", and does the same thing with "Fruits", it will count both columns, not just "Fruits".

How can I use the same principle, to create new columns, if they not not exist, but only based on exact matching search?

I attached a demo sheet that shows the problem.

Best regards
Imbizile

The data from the external link is missing.

OK - then I can't help as I don't understand what you want. Sorry.

Table looks like this:
AddNonExistingColumns.png

I need to check if there already is an existing column named "Colors", if so, it should do nothing, otherwise it should add a new column named "Colors".

Then I need it to do the same with columns "GreenFruits" and "Fruits".

This is the code is use in Table (2):

Please Login or Register  to view this content.

The first step goes well. List.Count(List.FindText(Table.ColumnNames(Source), "Colors" )) is 1, so it does nothing.
The second step goes well. List.Count(List.FindText(Table.ColumnNames(Colors), "GreenFruits" )) is 0, so it creates the column named "GreenFruits".
The last step does not go well. List.Count(List.FindText(Table.ColumnNames(GreenFruits), "Fruits" )) counts 3, as it finds both "Fruits", "GreenFruits" and "BlueFruits". Which means it tries to create a new column called "Fruits". But that one already exists, so it returns this error:
AddNonExistingColumns2.png

Instead of getting 3 counts on "Fruits", ie "Fruits", "GreenFruits" and "BlueFruits", I need it to only get 1 count, ie an exact match for "Fruits".

The expected result should be 4 columns in the end:

Fruits	BlueFruits	Colors	GreenFruits

Perhaps you could try List.PositionOf to check if the column name is in the list?

That function will return -1 if the column name you are looking for isn't found.

Please Login or Register  to view this content.

Another one, Add list of columns

Please Login or Register  to view this content.