I am fairly new to Power Automate - this is my first post - but I would so appreciate any help - I have read more articles and posts of solutions than I can count. I have been working through one error after another for a week, and I'm finally on the last step of the flow and I can't figure out what is wrong.
I have a flow that gets a txt file of real estate county data and extracts text into an array with the split function, and then uses filter array and compose actions to pull out the text. I formatted the array in a compose action, and composed its outputs to append the selected text to the array which I had initalized at the start of my flow. I then parsed the array into JSON (following http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow article)
I now have tried two versions of the flow:
(1) with the Parse JSON and Apply to each/Create item within an Apply to each loop which runs through the array - result is an error on the Create Item action.
(2) with Parse JSON and Apply to each/Create item outside of the Apply to each Loop - flow runs successfully but there is no data captured in the SharePoint list.
Side note: I wasn't able to get rid of the carriage return \r so I'm wondering if that is the issue... (I've tried every post to remove but can't although I was able to get rid of the \n by setting the enter key as a variable and replacing.
The schema for my json is:
Note: I removed the "type": in the schema bc of errors in data types and null values.
Here is a snippet of the compose action to create Json format, a second Compose for its output, and then appending to the output to an initialized array which is the value for the parse JSON action:
Here is the raw output of the parse JSON action:
Flow with error Parse JSON and Apply to each/Create Item within an Apply to each loop:
Overview look at flow - this is the flow that run successful but with no data:
Another side note: I have also tried removing the parse JSON and doing Create CSV table from the array and then create csv file; however it only exports the first row of data and then I get an error message that the file already exists.
Thank you Pstork1 for the quick reply!! I did this because of its suggestion on the web page referenced above. I originally had ["string","null"] as the type for all my fields, but was getting errors and became concerned the \r carriage return was causing issues. Here is the instructions I follow as to why I took out the type:
So I changed my type in the schema back the way I had it and it ran again without error (which is good news since it wasn't the carriage return before causing issues - thank you PStork1 for having to point out the obvious to me), but I'm still not getting data in the SP list even though the flow is successful.
Is it because the actions need to be inside the loop where the array exists (even if it is set as a variable)?
Here is the new schema:
Hi PStork: I'm honestly not sure how to confirm the loop is running on properties, so thought I would send you the detail of the flow, hoping I've probably made an obvious error that will jump out.
First steps:
Trigger flow and initialize variables
filecontent/filecontent2 as string
Enter as string - manual enter on keyboard
delimiter as string - ^
AutoNum as integer - set 0
Increment variable of 1
split as string - specific text to split the records in txt file
none - as string (not currently used in flow)
array as array
Second steps - get and "clean data"
Get file content - is a txt.file
set filecontent2 variable as body of txt file
Compose: C-URIcomp - decodeUriComponent(variables('filecontent2'))
Compose: Replace - replace(outputs('C-URIcomp'), variables('Enter'),'^')
Compose: Replacen - replace(outputs('Replace'),variables('Delimiter'), '')
Compose: splitecords - split(outputs('Replacen'), variables('r'))
Compose: RemoveLastRow - take(outputs('splitrecords'),add(length(outputs('splitrecords')),-1))
Step 3: Apply to each - based on outputs of RemoveLastRow
split2 is my 2nd attempt to remove the \r ghost return carriage (didn't work) - \n was removed in above steps
split3: split(item(), ':')
Compose: NumRec is variable AutoNum for a running count of the records
(After NumRec-v is a series of filter array and compose actions to hone in on line of text containing values I want in the array.)
Step 4: create array with values (not sure if these steps are necessary....)
Compose: action titled JsonArray to create format from the outputs of the filtered values above
Compose2: outputs of Json Array compose action for format
Step5: Append outputs to array and parse JSON to create Sharepoint list
Appended outputs from Compose2 above to an array variable
New Apply to each to Parse Json from array variable
Nested Apply to each below (which I fixed based on your helpful response!) from the body of the Parse JSON and dynamic values pulled into the Create Item section.
Bird's eye view (in case it is helpful):
Thank you PStork1 to help me even understand where to start troubleshooting! I deleted the Compose2 action and based the value for the varArray variable on the outputs of the JsonArray compose action I used to pull in values, so the flow is now this:
I am seeing a Parse JSON error which describes exactly what you suspected - that the values are still in an array! So where in my flow do I need another Apply to each loop to get to the properties of each item before the Parse JSON action.
Also, thank you for your patience helping me! I am truly grateful for your help and obvious knowledge!
Thank you for the tip - as you rightly guessed, I hadn't updated that. After testing and then pasting the run history value of the varArray variable, I now see that all of the data types came up as an array. (I composed varArray to look through all the results, but do not see duplicate values for any of the fields so am concerned if the \r return is causing issues after all.) You've been so patient with me so I feel badly asking one more question, but is there a function that can be used on an array which would enable me to combine what appears to be duplicate values (like a union or concat function for a string?)
I guess I'm just looking for a way to get the data values at the top level of the JSON schema.
Thank you again for all the guidance in helping me understand JSON schema.
Here is the new schema:
Helpful resources
Join us for Microsoft Power Platform Conference
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
A new place to submit your Ideas for Power Automate
Announcing a new way to share your feedback with the Power Automate Team.
Register for a Free Workshop
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Ask your licensing questions at the Power Automate AMA!
Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.
