Overriding column dynamization

(0) Share

Report

Posted on by ilanas

Good Morning,

I would like to know if there is any way to do the following with power automate.

When an excel is left in one drive, I pick up the rows from this excel. For example it has this format:

Date	Category	Probe1	Probe2	Probe3
08/06/2021	Apple	110g	120g	130g
07/06/2021	Orange	120g	130g	140g

I want to transform the data to have this format (like overriding column dynamization in power bi)

Date	Category	Atribute	Value
08/06/2021	Apple	Probe1	110g
08/06/2021	Apple	Probe2	120g
08/06/2021	Apple	Probe3	130g
07/06/2021	Orange	Probe1	120g
07/06/2021	Orange	Probe2	130g
07/06/2021	Orange	Probe3	140g

Once i have this format achieved, I want to insert each record in a Sharepoint list. It is necessary to get this formatting done because the number of probes can be very high.

Any solution?

Thank you,

Greetings

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

Sort by

jinivthakkar 4,187 on at

Like
a
(0)

Report
Copy link

Link copied!

@ilanas why do you want to format before the data is pushed to SharePoint, if you want to show/view data in alphabetical order, just create a view in SharePoint with your custom order.

If you still want to order then you can, use Run Script action which will have a script to format excel as per your need and then you dump in SharePoint.

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up

Was this reply helpful? Yes No
ilanas 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @jinivthakkar , The problem is that the original excel have more than 300 columns of probe1, probe2, probe3,probe 4... It excedeed the limit of columns of sharepoint. Moreover, I prefer only have 4-5 colums in sharepoint list to have all much clear. I don't understand what are you saying me with this "If you still want to order then you can, use Run Script action which will have a script to format excel as per your need and then you dump in SharePoint." I don't want to sort the rows,
just divide the data inside of it. Can you help me?

Was this reply helpful? Yes No
jinivthakkar 4,187 on at

Like
a
(0)

Report
Copy link

Link copied!

@ilanas we have an action, see below
If you know how to write a script which will get you only the data that you need, then you can just call the script and you will have your data
I really don't know how to write a script, else I would have definitely helped you.
Just see this and try

https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Was this reply helpful? Yes No
neerajsu 70 on at

Like
a
(0)

Report
Copy link

Link copied!

To add to what @jinivthakkar said.

Scripts are basically typescript code that you create in one drive and can also attach to excel files if you need. They can directly access data from excel sheet and transform and respond with whatever you need.

If you know a little bit of javascript programming (typescript is just a strongly typed programming language over javascript), here's a couple of youtube videos you can see to understand how it works. What you require can easily be done, if you do it via script rather than via power automate actions.

https://www.youtube.com/watch?v=il7YS7Dx228
https://www.youtube.com/watch?v=jBO1nGLeTQs

Was this reply helpful? Yes No
ilanas 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @neerajsu ,
But the problem is that if the excel is in the one drive, the excel online don't have power query so i can't use de unpivot function, no?

Was this reply helpful? Yes No
neerajsu 70 on at

Like
a
(0)

Report
Copy link

Link copied!
There is no concept of excel online or power query here. All of this is done using code.

The excel script you write will do the unpivoting and will return a json in this format to Power automate.
[ { "date": "08/06/2021", "category": "Apple", "attribute": "prob1", "value": "112g" }, { "date": "08/06/2021", "category": "Apple", "attribute": "prob1", "value": "112g" }, { "date": "08/06/2021", "category": "Apple", "attribute": "prob2", "value": "130g" }, { "date": "08/06/2021", "category": "Orange", "attribute": "probe1", "value": "110g" } ]

Go through the videos I referenced. It will make more sense for you.

Was this reply helpful? Yes No