Data Transformation

[Rebecca7]

Good morning, everybody,

I have a form in Microsoft Forms, which has several columns that contain the answers 'Yes', 'No' and 'Does not apply'.

Would it be possible, using Microsoft Flow, to create three Columns: 'Yes', 'No' and  'Doesn't apply' with the values of the column headings that I already have? In that case the result should be

Thanks

[ChristianAbata]

mmm @Rebecca7  shure you can using filter query to count the yes values from columns in your first excel, and then using that value to create a newone with the result of counts.

What you need is filter query see

instead my email you need to change it to yes, and then you can count how many yes you have in your column selected.

[Rebecca7]

Hello @ChristianAbata Actually I dont have an excel spreadsheet. I extract a Microsoft Forms answer and then I have to do this data transformation. Is that still possible?

I just put the spreadsheet for a better explanation of what i need.

[Rebecca7]

@efialttes Once again, I'm trying to do one more crazy thing on my flow rsrsrs. Can you check it out?

[efialttes]

@Rebecca7 

Please note @ChristianAbata is one of the top contributors to this community for the last months. His suggestions are wise...

Now on your challenge... would it be OK to store a set of counters for each 'Yes', 'No', 'Does not apply' per item (Balaclava, Botinas, Cadeado, etc) and increment them accordingly everytime you receive a Forms response? If so, can this set of counters be stored in a Sharepoint List? In a Excel file stored somewhere, as @ChristianAbata suggests?

Thanx!

[Rebecca7]

@efialttes @ChristianAbata I'm sorry. I didn't mean to bem rude, my intention was just to create a forum to discuss this question.

I really need this format below beacause of the graph that I want to build in Power BI later.

[ChristianAbata]

hi @Rebecca7  don't worry is difficult to express when we use just text.

So to aswer you, when I see your excel examples I tought that use forms to send the responses and this is working well so you have and excel with all of that data. 

Starting from here if you have all of that data what you need to do is create a new flow to get all of that, with the excel example flow that I show you.

If you don't have that excel data what you need to do is create an excel as you show in the example and then use my example to create another flow.

Take care about your trigger in the secod flow, because you are goint to need a schedure trigger to do this data extraction in a certain hour or day.

In the same flow you can use my example too, but after you add rows into you excel file please add a Delay Action to wait until your flow write in your excel then use Get rows as my example.

Is a medium difucult but it can be made.

[efialttes]

@Rebecca7 

I see your point but... I am afraid with Power Automate you can just add new rows to a table, so even if Power Automate can also update rows the logic would be quite complex... I am a bit more pessimistic than @ChristianAbata, but I guess he has though of a way to proceed I didn't figured out yet

Just wondering if a different excel table data model can be also valid for your Power BI processing... that is to store the info as daily counters:

Hope this helps

[Rebecca7]

Hello @ChristianAbata  I followed your suggestions but the output is not exactly what I need.

With your guidance I was able to count the amounts of 'Yes' or 'No' of my tabel. However, what I need is a little different.

I need to transform my data.

Assuming that I have a table full of columns with 'Yes', 'No' in the rows, like the example below

I need to create a column named 'Yes' and put the name of the previous column as rows in this table and new column like this example

I'm sorry about the mess. If I wasn't clear again, let me know so I can think of another way to explain

[v-alzhan-msft · Accepted solution]

Hi @Rebecca7 ,

Sorry for I'm afraid that the requirement couldn't be achieved in Microsoft flow currently.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.