cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
koperek7
Regular Visitor

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

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!

011842FA-46DD-4B31-AA3F-4D5871F478DF.jpeg

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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!

View solution in original post

v-bofeng-msft
Community Support
Community Support

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:""}
    )

vbofengmsft_0-1642484422726.png

 

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]})
)

 

vbofengmsft_1-1642484422731.png

 

Best Regards,

Bof

View solution in original post

9 REPLIES 9
Nogueira1306
Super User
Super User

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
Regular Visitor

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?

 

 

I dont think you can change the collumn type

koperek7
Regular Visitor

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?

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

koperek7
Regular Visitor

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

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!

boff
Frequent Visitor

.

v-bofeng-msft
Community Support
Community Support

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:""}
    )

vbofengmsft_0-1642484422726.png

 

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]})
)

 

vbofengmsft_1-1642484422731.png

 

Best Regards,

Bof

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (3,007)