DropColumns not really dropping the column

[FernandoTC]

Hi,

I have noticed this issue (or maybe behaviour) many times but now I'm stuck with it and I think it's time for me to understand what is really happening here.

To give some context:

• I want to update several records in a SQL table ('mant.equipos') by changing the field 'activo' from true to false.  Each record in that table has a foreign key 'idarea', and I want to update all records from one area. The fastest way to update all the records is to make a Patch to the data source and giving as an argument the "updated table" that contains at least the primery key of the table to update and the new values for the field to update. Something like this:
  
  Patch('mant.equipos',_updatedCol)​
  
  Being _updatedCol a collection with at least the fields 'idequipo' (primary key) and 'activo' which is the one i want to update.
• Lets add some constraints to the equation:
  • I don't want to add such thing as a new collection only for patch purposes because I don't want the app to have innecesary storage usage and also to keep thins simple.
  • What I am trying to do? Patch the table by giving as update argument the same table filtered by a variable '_selectedArea.idarea' that filters the equipments I want to update, and make the field 'activo'=false for every single record. So the code goes like this:

Patch(

'mant.equipos',
AddColumns(

DropColumns(

Filter(

'mant.equipos',

idarea=_selectedArea.idarea

),

"activo"

),

"activo",

false

)

)

So to clarify, I'm specifying as the record clause of the patch function the table 'mant.equipos' without the column 'activo' and with a new column 'activo' added to it which is false for all records. This ends in an error message saying that "there's already an 'activo' column".

SO HERE IS THE PROBLEMATIC WITH THE DROPCOLUMNS FUNCTION:

As far as my understanding goes, when I use the Addcolumns() function and I pass the first argument to it (the table filtered and with the column 'activo' dropped), this argument should not have a column 'activo' already existing, BUT lets give it the chance (maybe the name is already taken in the background or something). So, lets make clear that DropColumns work.

For the sake of the science I'll create a collection to show if DropColumns work:

This is the argument I'm passing to the AddColumns within the Patch function. But lets see, what do we have in this collection?

Great, not an 'activo' field in sight! So now, can I add a columns named 'activo' to this collection?

"A column named 'activo' already exists"

Now, look at the collection again, no 'activo' column in it.

So the question is: Why does this happen?

________________________________________________________________________________________

Also I know the real solution because if instead of doing the DropColumns, I use ShowColumns specifying all columns but 'activo' it works. Here yoy have the proof of it:

And the original problem solved here:

[Waveflux]

I'm having the same issue.  DropColumns doesn't seem to work, even thought the official Microsoft documentation says:

"To rename a column to an existing column name, first drop the existing column with DropColumns, or rename the existing column out of the way by nesting one RenameColumns function within another."

[Lübbe]

I just saw the same issue, a DropColumns didn't drop the columns, it's still there ...

Thanx for the hint with the ShowColumns ...

And some minutes later i realized with the help of the MS-documentation, that the DropColumns-Command doesn't change anything in the original collection, but generates a new created output table without the unwished columns ...

Same with the AddColumns-Command.