How to use Power Automate to parse a HTML Table and convert to JSON

How to #PowerAutomate to parse and convert a HTML table to a JSON array for further use in your cloud flows. Once the HTML Table has been converted the data can be added to a SharePoint list, Dataverse or Excel table.
If you found this solution useful, I'd appreciate a donation to the channel:
www.buymeacoffee.com/paulie

[@robofski]

I love these real world use cases that you demonstrate, amazing stuff!

[@PaulieM]

Thank you for the feedback Dan. I find real world use cases a bit more interesting as they bring unexpected complications. Glad you are enjoying the videos.

[@sashawarchoon1989]

THIS CHANNEL DEFINITELY DESIRES MORE SUBSCRIBERS!

[@ruthjmhughes]

Hi Paulie, thank you SO MUCH for this video. I am brand new to Power Automate and have been trying to extract table data from hundreds of emails for WEEKS now. This video has solved my problem. I cannot thank you enough.

[@PaulieM]

Thank you Ruth, so glad it was helpful to you.

[@asdrubz]

Uau, great job!! Thank you very much for the didactic explanation! Best of all about filtering content and handling text efficiently in Power Automate.

[@forsangram]

This video really saves a lot of time for me. Thanks a lot

[@leungchunyin4281]

best video to learn power automate step by step, not only show the result, step by step to work out the solution love it

[@PaulieM]

Thank you Leung 🙏

[@InFeRn03k]

perfect explanation, your videos are really usefull tnx for all

[@ConorMageeIrl]

Fantastic video - many thanks for your help. And not too fast all!!

[@MrKbid]

It took me 2 days but I got my slightly different tables and data to work thanks to this video

[@tuworlds]

this is probably the best video on youtube you got a sub from me i learnt alot on this vid thanks

[@BeInspiredAndMotivat]

Thank you so much, I was searching for days to find a solution for this

[@officeready6899]

Nice tutorial Paul!

[@Luvinist]

Thank you very much, great explanation.

[@aarunishekharjha5482]

Thank you for sharing your knowledge. Really helped me on my concepts. Thanks again!!

[@Kchula]

I rarely comment on people's videos but thank you so much for this VERY informative video. Whether you are a novice or need a refresher cuz you haven't done it in awhile (ME lol), this will help you with useful expressions which can be tricky at times. Thank you so much!!!

[@PaulieM]

Thank you Kenia, I really appreciate your kind comment. 🙏 😊

[@Kchula]

@@PaulieM Hi Paulie, I'm getting an error on the very last part. Are you able to help? 'The template language expression 'split(item(), '^')[3]' cannot be evaluated because array index '3' is outside bounds (0, 2) of array.

[@PaulieM]

@@Kchula try changing it to split (item(), '^')?[3] it’s because you’re trying to reference an element of an array that does not exist.

[@FranciscoCamacho_fjcamachoj]

Excellent video!!! Thanks. I watched another procedure but yours is quite simple and clear.

[@PaulieM]

Really pleased it was useful for you.

[@funnypirate5626]

Thanks a lot. it helped me a lot in my learning period . I am new to power automate and it is in such more details explanation and with examples. which helped me in my project.

[@shontinawesley432]

Bravo!!! This works great for me ...no issues! :)

[@ajssoarestube]

Best Channel.

[@FionaPascoe67]

Thanks so much with this example Paulie - sovled the problem that I've had - because it was real world made it so much easier to understand.

[@PaulieM]

Glad it helped.

[@AlanMartinezalnmb]

Thanks!!!! I helped me a lot

[@johnandrews3114]

Incredibly helpful video

[@PaulieM]

Thank you John - your feedback is appreciated.

[@jamiestreets7084]

Thank you this has helped so much :)

[@jamiestreets7084]

now im stuck trying to write this into an existing table within an excel template i have stored in sharepoint which then saves each table as a seperate excel spreadsheet which i plan on using each of these further to populate a master spreadsheet.

[@Ali-ds5iy]

Hi Paulie, This was wonderful presentation unlike any other I have seen here. Have become a new subscriver now! Just two important qs: 1) How do I remove any row during the 'Row' stage, For example I have one non-empty row in the end? 2) How can i append the output without the headers. Like I have already the headers in one excel file but simply want to append every output below the headers? Thx!

[@1mate2hip]

How do I select the second table within the HTML list instead of the last table using the last expression?

[@DreW-bm7fc]

Thanks!

[@DreW-bm7fc]

thank you, enjoyed every of your video, greeting from indonesia

[@dolfvc]

@Paul, thank you so much, this helped me a lot. 1 Question is that you used in htmlTables "concat('

[@koenmeijerink]

Thank you Paulie, great stuff! Xpath to me is still 'black magic', but I will figure out more on this in future I guess.

[@PaulieM]

You really don’t need to figure it out anymore. You can just ask Chat GPT to build the queries for you.

[@alok2k11]

Hi this video is really helpful... But I'm looking for a way to have table headers dynamically. Maybe My table will have 4 or 5 or 3 columns only. So How can we bind the array of headers and . I'm able to get column headers into separate array and respective rows into separate. But unable to bind them into one Select Action to make json

[@hushamahmed799]

Hello Paulie thank you very much for this video. I'm new to power automate can you please advise me how can I select table 2 or 3 after the using the split?

[@kylehelliar6293]

hey Paul, what if you have a table that has questions in column A and the answers in column B ? how would you make the JSON then if you are not using the column headers for the output?

[@shyampradhan5114]

Thanks for making this video❤

[@PaulieM]

Pleasure, glad it helped.

[@HappySingh-pj6wj]

Nice one Paulie, spot-on content :) I have a question Is it possible to extract from a .html file saved in SharePoint or email attachment .? Many thanks

[@thusithaariyananda1101]

@Paulie M, I am very new to this and trying to get this output to Share point list, How to do it

[@maanikarajmuthusamy2315]

Nice and thank you for your support

[@PaulieM]

No problem, happy to help.

[@Ali-ds5iy]

hi Pauli M, What if I do not want to select the last table but the middle one. What command to use then?

[@jigarpatel1189]

goat!

[@mryoyo1987]

Such a helpful video! If it works for me it would be so amazing. I completed all the steps of the video but i am not sure how to get the individual data points from the select step into Sharepoint items. I add the "create list item" and put the select item output in each of the columns. but it doesnt work. wouldn't i have to tell the flow which select item to put in the sharepoint field? seems odd putting the same thing in each. Your help (more of it, that is) would be so appreciated. AA

[@fanzfanzilla]

Hello, Thank you for the video. It's very useful for me. do you have a video for importing a CSV file that contains special characters?

[@ashotavetisyan1337]

Hi. First of all, thank you very much for a thorough walk of the process. I was struggling with this for a couple of days until I found your tutorial. For the step of mapping, I used a loop to dynamically create JSON items since the user might add stuff to the table and I didn't want my output to break because of that. So in my mapping in 'select;, I used this: split(item(),'^')[0], and element [1] as value. It worked for me, but I was wondering if there is a better way of doing this? Also a question. How can i return the JSON that i got after the 'select' action so i can use it further on?

[@PaulieM]

The output of the select action should be available in your dynamic content. You could use a parse json step after the select action - it would make things easier for you to reference.

[@ashotavetisyan1337]

@Paulie M thank you for your response. I was able to fetch the output and cureently struggling with parsing :) However, as a workaround, i used the condition block and am checking in a loop if the item contains the value. I would assume there should be a way to fetch values from the json obj, but im too beginner to that right away :)

[@wyattgreen4168]

How would you grab the URL that is inside of the brackets instead of the text?

[@germandeseta735]

Hello Paulie, I was following your video and doing step by step, everything was going well until I used the html to text connector. I got this alert "The flow was saved, but it couldn't be enabled because it contains connectors to apps that don't support the Gmail connector used in this flow" Do you know of a way to do that step differently? Thank you very much in advance for the video

[@lavanyaaravindhan6083]

Hi, Please share video like how to insert custom lookup field, custom yes/no field and custom choices field into Dataverse.

[@hamzaiqbal2512]

Hey Paulie, love the video great explanation. Is it possible to take out a column, like how you took out a the first row ?

[@PaulieM]

Use the skip expression to go remove the first element from the resulting array.

[@hamzaiqbal2512]

@@PaulieM and what step would this take place in ?

[@KasiaWichrowska93]

Great content!! What would you use if you only wanted to get first let say 5 rows from HTML table and ignore the rest?

[@PaulieM]

I would think it would be easier to get every row, and then use the "Take" expression in a compose action to get only the first 5 rows of output. Does that make sense?

[@KasiaWichrowska93]

@@PaulieM Absolutely spot on! Thank you so much

[@dhanishjain255]

Hi @Paulie, i am trying the step and getting error 'cannot be evaluated because array index '1' is outside bounds (0, 0) of array' on action: Select after filter array. The split expression 'split(item(),'^')[1]' onwards is not working but split(item(),'^')[0] is giving result. any thought?

[@investandcyclecheap4890]

same error :(

[@carolinagoes2137]

@@investandcyclecheap4890 split(item(), '^')?[0] missed the "?"

[@akshaykathuria06]

Hi Pauline, Great video. Just like others I'm also facing the same error The template language expression 'split(item(),'^')[1]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Any suggestions?

[@Ali-ds5iy]

Im having the same issue.!!...haaalp!

[@Ali-ds5iy]

oh just figured out...simply place the ? in between the array number and bracket closure.

[@daurenkassymov4892]

Ali, you're a legend! Thanks!

[@daurenkassymov4892]

Ali, you're a legend! Thanks!

[@BEATWINCONTROL]

@@Ali-ds5iy thanks! it worked for me

[@kylerodger6525]

Your videos are a life saver! But i'm getting an issue indexing anything other than element 0 on the Select step. It gives me error message: The template language expression 'split(item(), '^')[1]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Can you suggest anything I should check? I followed your other steps religiously. Thanks!

[@PaulieM]

Off the top of my head no idea. But if you want to do a screen share I will see if I can help you out? Get in touch via my blog

[@jasonechevarria1430]

@@PaulieM What was the fix for this problem? I am receiving the same exact error message. Thank you in advance.

[@PaulieM]

@@jasonechevarria1430 the person that asked the question had a mistake in the split. Are you stuck also?

[@jasonechevarria1430]

@@PaulieM Yes I'm stuck as well. I cannot figure it out.

[@PaulieM]

@@jasonechevarria1430 get in touch via my blog and I will do a session with you and help you out.

[@maheswarin2684]

can you please add the content of saving data to excel table

[@jeffwright357]

kzbin.info/www/bejne/f6i1fIqXidOfabc "There is a fancier way of doing this..." What is this method? With the new editor layout, I'm struggling a little to do this step to remove the slash N's.

[@dushy4486]

What if we don't know the column name like they are dynamic everytime

[@PaulieM]

You can collect them from the first row. And use them as the column headings

[@dushy4486]

@@PaulieM yeah but it can only happen when you have fix no. Of columns because you are indexing them. What if i don't know the number of columns that how many columns are coming from email table. how will I use indexing in that scenario??

[@PaulieM]

@@dushy4486 you would use the range expression to dynamically build it.

[@lavanyaaravindhan6083]

Paulie, how could we add the expression, sorry I couldn't get your point.

[@dushy4486]

@@PaulieM you mean we will put all the column names to an array then check the array length to create a dynamic range??

[@yann6757]

why MS doesn't provide a html parser, all those steps just to convert html is timewaste.