How to change the position of the rows in the datatable

Learn Academy Feedback
1

Hi Team,

I have a datatable with contents this;-

|Column1|Column2|Column3|Column4|
|Revenue from operations |A|B|0|
|Other income|0|0|0|
|Cost of raw materials and packing materials consumed|0|0|0|
|Changes in inventories of finished goods and work-in-progress|0|0|0|
|Employee benefits expense|0|0|0|
|Finance costs|0|0|0|
|Depreciation|0|0|0|
|Other expenses|0|0|0|

Here I have to change the position of the rows like
if first column of the row contains “Other income” then this row will come on first like this.

Means here row 2 has “Other Income” so it becomes the first row in the output data table

2

@Karan28 What are the other conditions ? Is there a Specific order you have to follow throughout ? Will there be any other row with the same values present in the Column1 ?

3

Yes there will be specific orders and the rows will be unique.
for example
if the row(0) contains Finance costs
then that full row comes on the third position in the datatable.
if row(0) contains depreciation then that row will come on the 5th position.Basically it is the rearangement of rows on the basis of values in column A

4

@Karan28 If it always that particular order, can you provide the order for the above table ?

5

@Karan28
one option could be to do it with insertAt

so if the particular row is not on first position insert it and remove origin

2 Likes
6

Hi Yes I can tell you that

this is the order

Revenue from operations
Cost of raw materials and packing materials consumed
Employee benefit expenses
Other expenses
Changes in inventories of finished goods and work-in-progress
Depreciation
Other income
Finance cost

7

in that scenario (also many reorders can happen) two approaches are available:

and here: DT_CustomFilter_DictApproach.xaml (10.0 KB)

8

@Karan28 Check this Workflow :
Test it for all possible cases. But I have used an Additional Package that needs to be added.

You can download it from the Manage Packages. If you don’t want to use it. We can come up with a Different approach. But mainly check if the Output is the expected one.

TransposeRows.zip (2.1 KB)

1 Like
9

Okay I am checking both the solutions and will update you soon

1 Like
10

Hey , I have seen your solution its kinda good but doesnt satisfy my condition , its like if some column A contains "Revenue from operation " then out that on row 1. So I cannot use equal I have to use contains only because sometimes string differ little bit like operation and operationS so I have to use contains only.

and second is if I dont find “Revenue from operations” then I should leave that condition and check for other string

11

@Karan28 Yes. It does have some restrictions :sweat_smile: But I’ll try to modify that, I think you can use the other method provided. So The row Order will remain the same right and if the row values are missing for a particular keyword, then just include the remaining in the same order . Is that right?

12

Yes you are right, But it should be dynamic. Like if it presents take it or leave

13

Hey , could you please explain little more on your solution. The xaml file you have attached

14

@Karan28 Check this Workflow :
A little bit of modification had to be made to choose the Columns Existing. You can try and revert back. You can provide feedback again if it doesn’t satisfy the requirement.

One row is missing in the Build Datatable
TransposeRows.zip (2.7 KB)

15

HI , I have seen this . Its better than before.
but Can I use contains logic here
{“Col 1”,“Revenue from operations”,“Cost of raw materials and packing materials consumed”,“Employee benefits expense”,“Other expenses”,“Changes in inventories of finished goods and work-in-progress”,“Depreciation”,“Other Income”,“Finance costs”}

for example it will check like if it contains “Revenue” then that row goes on first position

16

@Karan28 Yes. Just replace them with your Keywords.
It can be in this way as well. But make sure it is unique.

{“Col 1”,“Revenue”,“Cost of raw materials”,“Employee benefits ”,“Other expenses”,“Changes in inventories”,“Depreciation”,“Other Income”,“Finance costs”}

17

Yes it will be unique, I will try thanks again

1 Like
18

This is the great solution now ,Thankyou so much.
I have one thing to add here , so I neeed to add the first 4 rows here and the sum of first 4 rows will be in the 5 row.

1 Like
19

I did not understand that, can you explain it in more detail :sweat_smile:

20

Okay

Suppose the final output datatable I get after you logic.In that final datatable I have to add first four rows and the sum of those rows comes in the 5th row. like {“Total”,100,200,300} where 100,200,300 is the sum of above four rows numbers .numbers will be in negative or positive in all the rows