Split ; delimited content of an excel table cell for use in a flow without using macros and fully online

[GillianEd]

Hello,

I am a complete newbie to all this.

I have a spreadsheet that is populated from a form submission that triggers a Power Automate flow. The form deliberately generates multiple responses in six of the cells in the newly created row. These come into the table as ';' delineated text - eg

Learners in my courses are regularly required to complete activities when engaging with information/resources, not just read or watch, even in theory components.;I confidently use a small variety of strategies for learning such as explanation, diagrams, demonstration, reading, listening, viewing, discussion, role plays and scenarios.;I outline clear learning objectives, linked to the knowledge and performance criteria in the unit of competency, for every session.;Learners in my classes can see clear links between the engaging tasks they are doing, the learning objectives for the session and the workplace.

I have spent four days trying to split this text into four adjacent columns, so that I can use VLOOKUP to replace them with specific scores which are then used to do calculations and create charts.

I can do this in multiple ways, but none automatically update when new responses are submitted.

I can't use TextSplit function in Excel as it doesn't work in 365 for Business - that would be the best/easiest solution if it auto updates!

I can't use the Text to Columns feature in Excel in a fully hands off automated process, and can't use macros (organisation won't allow)

I have used PowerQuery - perfect, but it needs to be refreshed before new data updates come in, and I can't seem to automate this process. I am working in a sharepoint folder, and don't have access to PowerAutomate Desktop which does the refresh easily.

I tried to add in an automatic refresh in the VBasic for the source sheet to refresh all - and found that is a macro.

I have used =TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(D3," ","</s><s>")&"</s></t>","//s")) in Excel - perfect, but as soon as you introduce tables (which I need to do, because after some lookups and calculations I need to transpose the columns and rows to Create a  Chart in Power BI) it stops working and I get spill errors.

I have used PowerAutomate combining Parse_JSON, split(outputs('Compose')';'[0] - which almost works, but returns too much for the first sentence (because there is no ';' at the start of the sentence, it returns ID, name, email address etc as well as the first sentence. It would be perfect if I could apply this split to just one cell or column but I can't find any way to do that without using transformations in Excel (then can't refresh) or Power Automate Desktop (which I don't have and I think won't work. Alternatively, if there is an easy way to add a ';' delimiter at the start of the cell using formulas in Excel that will automaticallu update when new data comes in to the table.

I am seriously running out of time and fear I'm going to have the same problems - sharepoint files, Excel without macros, Power Automate but NOT the Desktop version - when it comes to the rows to columns transformation to create the Radar Chart. Any suggestions or code would be gratefully received!!!

[lbendlin]

trying to split this text into four adjacent columns

Don't do that.  There is no guarantee that the order of the values will always be the same.

Instead, flip your data on its side.  Return rows of key/value pairs.  That way neither the number of responses nor their order matter.  This will also be easier for your eventual goal (the radar chart).

[GillianEd]

Hi Ibendlin,

Thanks for your response. I don't really understand your response. I just looked up key/value pairs and I think I would need a database. 

I am just using Excel, PowerAutomate, Microsoft Lists and Power BI. At the moment, people will select 4 responses from 12 options for each question in a form. These come in as a spreadsheet, with the 4 responses together in a cell separated by ';' for every question. 

I am trying to split those responses, so I can use a VLOOKUP to allocate the appropriate score for their response. All possible responses are managed in a list which is used to generate a table and matching score either 1, 2 or 3 points. If I split the responses and use a VLOOKUP then the order doesn't matter and I return 4 scores for each question, that I can average and convert to a percentage to chart. Is it this part that I should be doing differently, and if so, where do I start? Or is the whole model bad?

I then transpose rows to columns to create a radar chart.

Cheers,

Gillian

[lbendlin]

Or is the whole model bad?

Don't want to discourage you but I can also not disagree with that 🙂 

Would you be able to provide sanitized sample data?

[GillianEd]

Sure - I'm not sure if this will work outside our environment but I'll try. So this all works in excel, but I'm trying to automate it, so the submission of the form response triggers the flows and ultimately sends an email back to the respondent with the chart and the relevant text (depending on their response scores for the various questions)

[lbendlin]

This is how it would look like in Power Query

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Delivery Domain Snapshot example.xlsx"), null, true),
    FormResponses_Table = Source{[Item="FormResponses",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(FormResponses_Table,{{"ID", Int64.Type}, {"Start time", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type text}, {"First Name", type text}, {"Work email", type text}, {"Select the four descriptions that best describe the ways you develop engagement with learning that reflects training product requirements at TasTAFE.", type text}, {"Select the three descriptions that best describe the ways you develop learners' digital skills and digital literacy at TasTAFE.", type text}, {"Select the four descriptions that best describe the ways you include all learners in your facilitation at TasTAFE.", type text}, {"Select the three descriptions that best describe how you collaborate with other TasTAFE staff to provide relevant learning experiences.", type text}, {"Select the four descriptions that best describe your use of explicit teaching strategies to address and support foundation skill development in learners.", type text}, {"Select the three descriptions that best describe how and when you contextualise learning to learners, the environment and industry requirements.", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Work email", "First Name", "Name", "Email", "Completion time", "Start time", "ID"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Select the four descriptions that best describe the ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Select the three descriptions that best describe the ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Select the three descriptions that best describe ","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Select the four descriptions that best describe ","",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value3", {{"Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Value] <> ""))
in
    #"Filtered Rows"

including some prettying up/removing some of the text. The main transforms are the unpivoting and the splitting into rows.

You could do the same in Power Automate but it would be much more convoluted.  Consider switching to a Power BI based solution (or run the Power Query in Excel).

[GillianEd]

Hi lbenlin, I so appreciate your help here. 

I have tried a few options, but just wanted to check - do I create the power query in the Form_Responses tab, on a new sheet, or in a new spreadsheet?

Cheers,

Gillian

[GillianEd]

Spoiler

Thanks, I've realised I'm creating the query from the FormResponses Table!
I shall persist 🙂

[GillianEd]

Ok, thanks - that worked and is a much cleaner to have the data. It still will need the Excel file opened, or the PowerQuery refreshed, as each new response is submitted, though, and I can't seem to do that automatically without PowerAutomate Desktop (currently using the Web version). Any ideas??

In the meantime, from here, given there are 700+ respondents, would it be best to do the lookup scores in Excel and dump those into a Microsoft List, and then use Power Automate and PowerBI to generate the chart and combine the chart and appropriate text in a document and email back to respondents?

Many thanks,

Gillian

[lbendlin]

Disclaimer:  I am biased.

I would do everything in Power BI.  Connect Power BI directly to Forms, do the Power Query transforms in Power BI, load the data directly into the radar chart. Give the respondents access to anonymized cuts of the data.

It's your business process so you can decide how high or low your pain threshold is.  The purpose of automation is to make your life easier, not harder. So whatever works for you, and makes your life easier, will be a good solution.

[srduval]

I agree with @lbendlin reading through this thread, you are forcing yourself into a corner by trying to automate excel, it just won't ever work the way you want. If you have power BI available I would 100% run the refreshes in there and then you can automate the export of the power BI report to excel if you must have an excel sheet at some point. 

[David_MA]

I must be missing something based on the thread, but why can you not use the split command in a compose action to split your values on the semicolon? I created a manually triggered flow with your sample content and it works.

The expression in the Compose action is the following. You just need to replace triggerBody()['text'] with the data from the cell in your spreadsheet.

split(triggerBody()['text'],';')

 This found the four entries in your sample:

[GillianEd]

Hi David,

This sounds really easy, but I can't seem to specify the cell with the text in it. I only seem to be able to get a row. What code do I use to get the content of a specific cell?

Cheers,

Gillian

[GillianEd]

Hi lbenlin,

The problem is, I am not an IT person at all. So, I set up the process in Excel, as a model, because I could work that out, and that's where the form data arrived. Then I was told we couldn't use macros in the organisation.

So, then I tried to use Power Automate to replace the macros - I was going to use it to trigger everything when the form was submitted and to send out the results - hoping I could make everything happen seamlessly. Then I found out I can only use Power Automate, not Power Automate Desktop - which limited my options - particularly the refreshes. I didn't know PowerBI can automate processes - I was just using it (with difficulty) to try to replicate the radar chart produced so easily in Excel. Still challenged with that part - I don't know how to add a variable (name) and (date) to a label or title in the chart that is separate to the axis labels!

I have been on such a steep learning curve for the last four weeks.

If Power BI will do the splits, lookups, averaging, charting and send out the reports easly, then I'll give that a go. Thanks!!

[GillianEd]

Thanks, Srduval. 

I do have power BI! But I have only used it to work with data in Excel sheets. So how do I get the data coming in on the Microsoft forms to trigger power BI and where will that data be, if not in Excel?

Cheers,

Gillian

[GillianEd]

My earlier comments might clarify my issue a bit more - 'I have used PowerAutomate combining Parse_JSON, split(outputs('Compose')';'[0] - which almost works, but returns too much for the first sentence (because there is no ';' at the start of the sentence, it returns ID, name, email address etc as well as the first sentence. It would be perfect if I could apply this split to just one cell or column but I can't find any way to do that without using transformations in Excel (then can't refresh) or Power Automate Desktop (which I don't have and I think won't work. Alternatively, if there is an easy way to add a ';' delimiter at the start of the cell using formulas in Excel that will automatically update when new data comes in to the table.'

[GillianEd]

Ok, have just had a closer look at PowerBI, and am going to have a go to replicate my processes there. Any tips/suggestions for the most effective/efficient way to do this would be appreciated. Also, in terms of managing/storing the scores, charts and emailed documents - should I add them to staff member Lists or can you suggest something better? I would like to be able to chart and compare annual versions of the same survey for each staff member.

[lbendlin]

You can keep your Excel files as data storage. Place them on a sharepoint/OneDrive so Power BI won't need a gateway. Use the query that I posted to convert the data from Excel into a usable format.

Find out what your users are most comfortable with/what is most efficient.  You may not need to send emails, for example, if they are comfortable with running Power BI reports themselves.

[David_MA · Accepted solution]

I don't have a scenario for this, so I created a flow to figure this out since it is possible, I may encounter this. Although, I am still not 100% sure what you need to accomplish. But, what the following:

• Will get all of the rows in a spreadsheet where it stores responses that are semicolon separated.
• The spreadsheet has corresponding columns to store the individual responses from the combined response.
• Once the responses have been split, it will evaluate the response and store it in the column corresponding to the response.
• To do this, you will need to add a calculated column to the spreadsheet named Row with the expression =ROW()

In the end the workflow will result in the following in your spreadsheet. Note: I only completed the workflow for the first response in your sample data as a proof-of-concept.

These are the actions in the workflow:

The first compose action has the following expression (Response is the column with the combined responses in the spreadsheet):

split(items('Apply_to_each')?['Response'],';')

•  Apply to each 2 has the output of the first Compose action.
• Compose 2 contains the Current item from Apply to each 2
  
• The switch uses the output of Compose 2
• For the switch, you will add a case for each of your different responses and do the same thing under each with the Update a row action
• This is how the Update a row action is configured:
  

I hope this helps and makes sense. Note, one problem I had initially was it wasn't finding a match in the switch. I went into the run flow and copied the value in the Compose 2 action back into the switch:

Maybe I didn't initially enter the response correctly, but copying and pasting the value from the flow did the trick.

[GillianEd · Accepted solution]

Thanks all for the input. 

I have now abandoned using Excel and am working entirely with Lists, Power Automate and Power BI. 

Have FINALLY solved the split and then get rid of unwanted initial and final punctuation. 

When the split() expression is used, it returns an array - basically in the form

["Section before the delimiter,

Next section,

Next section,

Final section"]

To get rid of those [" and "] the trick is to follow 4 steps.

• 1. Split the initial_input into sections by

        Compose an expression    split'(outputs('body'],'","')  where you choose the body of text to split from          under create item in Dynamic content, and the character between the " " is the delimiter. which creates one more section than you have delimiters.

• 2. Initialise a variable for each section - selects the first item in the delineated list and turns it into a string, then the second etc

Name Section1

Type String

Value outputs('Compose_Input_Section1')[0]

Name Section2

Type String

Value outputs('Compose_Input_Section2')[1]  etc  

(The number in the [] is one less than the expression you are working on. First expression [0], second [1] etc)

• 3. Compose the output of that string Section1String
• Input - from Dynamic content choose the variable initialised above {x}Section1

• 4. Compose the final section without unwanted punctuation ( [" in first line in the array - Section1 and "] in the last - Section4 with ' '  ie nothing)
• Compose an expression replace(outputs('Compose_Section1String'),'["','')
• Compose an expression replace(outputs('Compose_Section4String'),'"]','')

The replace bit after the comma is in the form  ,  single quotes ' followed by whatever you want removed [" followed by single quotes' then another comma , and then single quotes 'what you want in its place - which could be other punctuation, a space, a different word and then single quotes again'   so the final '' is two single quotes with nothing between not double quotes.