Replace in table

[Boynog]

Hi Good day everyone!

How can I replace this the data within the collection table.

here's my current collection table 

    Group             Name                Age

color	red	age
#N/A N/A	orange	30
#N/A N/A	yellow	27
#N/A N/A	blue	57
number	one	20
#N/A N/A	two	#N/A N/A
#N/A N/A	three	#N/A N/A
food	kimchi	43
#N/A N/A	fried chicken	20

This table below I want to achieve.

    Group              Name                Age

color	red	38
color	orange	30
color	yellow	27
color	blue	57
number	one	20
number	two	20
number	three	20
food	kimchi	43
food	fried chicken	20

Thanks in advance everyone.

[v-xiaochen-msft · Accepted solution]

Hi @Boynog ,

According to your description, I've made a test for your reference:

1\ This is my collection ‘Test1’.

Add a button control and set its onselect property to:

ClearCollect(Test1,{Group:"color",Name:"reed",Age:"38"},{Group:"#N/A N/A",Name:"orange",Age:"30"},{Group:"#N/A N/A",Name:"yellow",Age:"27"},{Group:"#N/A N/A",Name:"blue",Age:"57"},{Group:"number",Name:"one",Age:"20"},{Group:"#N/A N/A",Name:"two",Age:"#N/A N/A"},{Group:"#N/A N/A",Name:"three",Age:"#N/A N/A"},{Group:"food",Name:"kimchi",Age:"43"},{Group:"#N/A N/A",Name:"fried chicken",Age:"20"})

2\ Add a button control and set its onselect property to:

ClearCollect(Test2,{Group:""});Clear(Test2);ClearCollect(Test3,{Age:""});Clear(Test3);ClearCollect(Test4,ForAll(Test1,If(!("#N/A N/A" in ThisRecord.Group),Collect(Test2,{Group:ThisRecord.Group}));If(!("#N/A N/A" in ThisRecord.Age),Collect(Test3,{Age:ThisRecord.Age}));If("#N/A N/A" in ThisRecord.Group&&"#N/A N/A" in ThisRecord.Age,{Group:Last(Test2).Group,Name:ThisRecord.Name,Age:Last(Test3).Age},"#N/A N/A" in ThisRecord.Group&&!("#N/A N/A" in ThisRecord.Age),{Group:Last(Test2).Group,Name:ThisRecord.Name,Age:ThisRecord.Age},If(!("#N/A N/A" in ThisRecord.Group)&&"#N/A N/A" in ThisRecord.Age,{Group:ThisRecord.Group,Name:ThisRecord.Name,Age:Last(Test3).Age},{Group:ThisRecord.Group,Name:ThisRecord.Name,Age:ThisRecord.Age}))))

3\ The result is as follows:

// Test4 collection

Best Regards,

Wearsky

If my post helps, then please consider Accept it as the solution to help others. Thanks.