Add new column and value under it based on the condition from previous column

[koperek7]

Hey,

I’m struggling with one, probably simple thing, but I tried so many functions and none of them worked, so I assuming that something is not okay with my way of building functions,

I have a collection as attached, what I want to achieve is replace column Status with text value according to the rule.

If ‘status’ = 0 then replace value 0 with Cat

If ‘status’ = 1 then replace value 1 with House

If ‘status’ = 2 then replace value 2 with Car 

If ‘status’ = 3 then replace value 3 with  Dog

If ‘status’ = 4 then replace value 4 with  Glass

so it can be done within the same column, but I don’t know if it’s possible if currently the column is of data type number, but also it can be done with totally new added  column, it doesn’t matter so much. Any ideas??

thanks!

 

[Nogueira1306]

You have 2 options:
1. Patch

2. Delete and add new

With first one would be something like this:
Patch(

Collection,

LookUp(Collection, condition),

{Status: '...'}

)


2 option:

Remove(Collection, LookUp(Colleciton, Condition)

Collect(

Collection,

Defaults(Collection),

{

...

}

)

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[koperek7]

Hi, thank you for the solution, I didn’t know about patch function before. Unfortunately your first solution didn’t work for me, whenever I try to update column Status with a new value, then it can be only number value, as it’s numeric column, so how it can be updated with a string value? May I change the column type somehow?

[Nogueira1306]

I dont think you can change the collumn type

[koperek7]

Ahh, not good - so is there any other solution having in mind that my column is of number data type, and I want to change it to a string?

[Nogueira1306]

Create other collumn maybe... Like:
Status_int and Status_string

[koperek7]

I’ve created it, but do you have any idea for the expression/formula for this newly created column.

so one column is with int 0,1,2,3,4 and the second one is string column with corresponding values - but how to write an expression like: if it’s 0 write Cat, if it’s 1 write House etc.

thanks

[Nogueira1306 · Accepted solution]

ForAll( CollectionName,

Switch(

Status,

0,

Patch(

Collection,

LookUp(Collection, condition),

{Status: 'Cat'}

),

1,

Patch(

Collection,

LookUp(Collection, condition),

{Status: 'House'}

)

)



Just add the conditions and the collection/DB

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[Anonymous]

.

[v-bofeng-msft · Accepted solution]

Hi @koperek7 :

If there are many rows in you data source,I suggest you use Updateif function to update the records  in bulk.I've made a test for your reference:

1\I assum there is a data souce:

ClearCollect(
    YourDataSource,
    {Status:0,StringColumn:""},
    {Status:3,StringColumn:""},
    {Status:2,StringColumn:""}
    )

2\Up date the records

ClearCollect(
    TheCollection,
    {Status:0,Text:"Cat"},
    {Status:1,Text:"House"},
    {Status:2,Text:"Car"},
    {Status:3,Text:"Dog"},
    {Status:4,Text:"Glass"}
    );
ForAll(
    TheCollection,
    UpdateIf(
        YourDataSource,
        Status=TheCollection[@Status],
        {StringColumn:TheCollection[@Text]})
)

Best Regards,

Bof