Reza, Power Apps, Power Automate and SharePoint are my career choice and passion. Because of work related diversions such as meetings, reports, administration and support, I don't have the time to experiment and learn expert level development on my own. You and Shane have filled that requirement over the years to help make me successful. I owe you both a lifetime of gratitude. Thank you!
Reza, thank you for all your input. For me as a non developer all your input puts me in a position to create complex applications in a really short time! Thanks for all your short and concise lesson to this world!
Thank you so much yet again Reza! I have had a similar requirement before. The way I did this was I exported the list using the Export List View feature of SharePoint. Then I uploaded the downloaded IQY file into a SharePoint library. Then in powerapps, I allowed the user to download the iqy file directly from the power app. Caveats are that the iqy file cannot be used on Mac devices. Also, the custom filter from within powerapps can only be done by creating multiple views in the SharePoint list.
@@RezaDorrani yes. I love your method for that reason. I have done this before by sending the JSON to power automate and parsing it but it was slow and hard to maintain. Your method alleviates those issues.
Thanks for the clear explanation. This will be useful for a milage tracking application I've built which is to include a feature of sending a manager a report of the trips taken that are still marked as "Pending" in the SharePoint list. I think I'll include a step that marks the status from Pending to Submitted once the report is sent. As usual, Reza, you have been a wealth of information.
Thank you really much Ezra for sharing this amazing video. Just a question: how to export data if the gallery filtering is carried out by using two different TextInput boxes?
Whatever filtering logic you have, you would need to recreate in flow. Goal will be to pass the data (multiple params if needed) to flow and then frame your odata query.
Hi Reza, this is a really comprehensive tutorial. Thank you so much. I would like to ask what would be the formula to get all items if I did not input anything on the dropdown filter? Thank you in advance.
There would be no filter condition in that case in the flow. Pass some param that says all data needed, use it in flow and do not apply any filter condition.
@@RezaDorrani Thanks a lot. But I'm with the same question. If I put some filter in Title but in the drpProgress i don't put anything, it will not return anything as I don't have blank in Progress column. How can I make the sharepoint just look to Title in this cases? You have a video that shows how to filter dropboxes with blank Or(IsBlank(DrpStatus.Selected.Value);Status=DrpStatus.Selected.Value) but doesn't works for sending to Power Automate. Can you help? Thanks in advance.
@@GoncaloMig I would have to recreate your use to case to provide guidance. I would recommend posting your query on forums at powerusers.microsoft.com/
Hi Reza, Thanks for the video. If you want to export all the data in the list without filtering, what parameter should we write instead of Export.Task.Run(startswith)?
Hi Reza, thank you for the video, one question, how would you center HTML table in the email body? Now, table align to left, what if I like table in center of email? Thanks
Reza, thanks for the explanation 😊 I do have one question, when I get the CSV file from the mail and when it is opened, all the data is in one column separated by commas. How can this is be avoided and ensure the headers are in separate columns.
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Welcome! If not shown in video then I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza. Thank you for that video. It is always clear and helpful. May I ask you a question related to the query... I have a data table (data from a SharePoint list) with multiple dropdwns and search box used to filter. It is a large query with switch + search + filters (7 dropdowns with IsBlank condistions,...)! Is it possible to copy/paste the query as is and use it in the Run function for the export ? Is there any other actions to perform to pass the query into Power Automate. Thank you for your help.
Hi, Reza. Great and helpful video, as always! For the PDF part, I noticed that the page breaks weren't properly formatted (or probably I'm not used to seeing it). I would like to ask if you have any suggestions/workaround on One Drive converter to PDF having page breaks (and page numbers if possible).
There is no workaround for that page break issue. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza thanks for this great content. I have a question: what is the filtered data is a choice how do we call that in Power Automate and also export function in powerapp?
Hi Reza, thank you for this incredible solution. How do you handle Filters on blank searches? If I do not use search and use a dropdown I return 0 from Sharepoint list. Thank you!
hi Reza, thanks for the video, really its awesome. btw, can we export the sharepoint data without any filters, i mean i wanted to export the entire available data from sharepoint to excel.
@@RezaDorrani thanks reza, i have the gallery in my app which is filtered either by Title (or) Client Name (or) Status using 1 textinput box. can we export the filtered data alone to excel? in that case what query we need to use in ExportData.Run(). Currently i use the below formula but flow not running properly it gives error. ExportData.Run("startswith(Title,'"&TextInput1.Text&"') or startswith(Name of Client,'"&TextInput1.Text&"') or startswith(EM Status,'"&TextInput1.Text&"')",User().Email); Below is the error message in Flow: The expression "startswith(Title,'UPS') or startswith(Name of Client,'UPS') or startswith(EM Status,'UPS')" is not valid. Creating query failed.
StartsWith is an inbuilt function in Power Apps. Not sure why it would not work for you. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
As per usual, excellent information!! Do you have a video showing how to do this, albeit with small data sets, but group on a lookup field? I seem to recall you having done something like that in past.
Thanks for this Reza, I followed your guidance for launching the flow with two queries in my case two choice values, when I try three, I just cannot get it to work. I just added the third the same way I added the second in the video. A bit stumped at the moment.
@@RezaDorrani Thanks for the reply, I figured out why it was not working although still a bit odd. I had to use the display name (internal name) which was this 'Sub_x002d_Category_Choice' instead of 'SubCategory_Choice'. Strange one of the other columns I used was this 'Category_Choice' which is also the Display name. I think I might of changed the column from 'Sub-Category_Choice' to 'SubCategory_Choice' which might have caused this. The code now looks like this: LifeAfterCollegeExport.Run("startswith(County_Choice, '"& cboMFilterCounty.Selected.Value &"') and Category_Choice eq '"& cboMFilterCategory.Selected.Value &"' and Sub_x002d_Category_Choice eq '"& cboMFilterSubCategory.Selected.Value &"'",User().Email); Notify("Export process started. You will receive an email once completed.", NotificationType.Success,3000)
Thanks, this worked great but one question: One of the fields I am exporting is rich text and I want to remove the html tags when exported to the csv. I have tried multiple methods but to no avail. Do you have any ideas on this one? Any help is greatly appreciated.
Hi Reza Hope you are well. Thank you for the great content. Do you have work around in power automate for a SharePoint list that is larger than 5000 records. I am looking for a work around well I am trying to convince my boss to move to dataverse. Thank you
This video can query large lists as long as the result returned after query is less than 5k records. You can also get more records by turning on pagination for the get items action.
quick question Reza - if I want to add a text field to the page filters (a combo box) but add to the Query dynamically (field is defined as a single line text in SharePoint) how would I write the variable? field_name eq '"& variablename.Text &"' or would it be variablename.SelectedItems.Result or Selected.Value? I have tried various iterations, but power apps seems to be indicating it's an error in the script. Thank you for your video - it's been helpful. AA
Should be combobox.selected.Value or whichever property you want to use. I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thank you my mentor Raz, At 18:00 how to code for and Status =dropdownlist.selected.value or Status = dropdownlist.selected.value = blank() I have to dropdown list and the 2nd drop-down is dynamic.
I have done videos on odata filter queries. Check those to see how to frame multiple queries. Alternatively, you can also try posting your query on forums powerusers.microsoft.com
Reza, This is great. I had one question. How would one use something other than "startswith" for the export criteria? I currently have a search that uses the "in" function and I'd like to still be able to filter based on that. Is this possible?
Love this one Reza...it helps me a lot right now on my project! Only ting I miss is how to get items from multi filter if I dont choose anything from dropdown filter (e.g. I want all items to be returned in Flow)? I'll try to get answer in your another video "OData Filter Query"! Keep up with great job! ♥
Hi Reza, great video tutoria! One question, how can add borders to html table in pdf file? I see in your email attachment you have borders in pdf file.
My first time comment to your video. This really helpful. But, how do I pass date to the query. Seem like only text is allowed to be pass in the query.
Hi. Great explanation as always. Is it possible to combine several galleries or some other way include a totals box at the bottom? For example, I have several filtered sum columns which I’d like to include at the very bottom of the main data? Many thanks.
Possible. But I do not have any reference videos on this topic. You would definitely run into delegation warnings. Check on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza, thanks for your help. In my case, I had to use a table instead of a gallery to show the informration as it was a lot. How can it would change as I was trying to use the same formula. thank you in advance.
Hi Reza A small suggestion, it'll be super helpful if the video content also have a blog post (or screenshots of processes ) for a quick reference. Thanksss a ton!💗💯
Hello Reza, Nice video I was wondering can this been done to generate a csv file instead without using flow and send it with SMTP.SendEmailV3 with this attachment?
Hi, Nice video and great explanation. But i have one question when i am getting more than 5000 records using pagination method in power automate i get error
@@RezaDorrani Thank you your reply. If I am not selecting the dropdown or date or any field. I don't want the query to be created for that field is it possible
@@pune2hyd For the query scenario, you would need to write logic in the part where we are passing the query param. You will need to add blank check in there.
Awesome video Thanks Reza i would like to get all styling from my powerapp while export like an extension to print functionality to export all data to pdf is that possible?
Thanks Reza! This works like a charm. However I have one minor issue, when I'm downloading the CSV File and opening it into Excel, there are quite a few special characters like "’" in some of the cells, along with the information I need.
Not sure about those. Might be some special character related issues. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thanks. My guess is because something was done right. I will have to look at your flow in action to know the reason for your issue. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Thank Reza, this video can helpfully, I just want to clear my data from gallery is not appear in email but the header can appear in csv and HTML table style? please give me advice
I will have to look at your App in action to provide guidance or reproduce your scenario to know more. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza. This tutorial seems pretty straight forward. I followed it to a T. However, I cannot get the flow to run successfully. It keeps getting stuck on the Get Items. The expression "startswith(Title,'test')" is not valid. Creating query failed. PowerApp button code is ExcelExport.Run("startswith(Title,' "&Searchbox.Text&" ')",User.().Email). Suggestions?
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza, Thanks for the video However i was wondering how we are going to achieve this with a combobox(multiselect). How can we write the query for multiselect values? Should we write for the each value one by one? For ex: by using Last and FirstN function? But if we have a large number of dropdown values, ex: countries and it is multiselect. Then how should we achieve the filtered excel report? Thanks again in advance.
I dont think I have a video reference for this scenario. Check documentation on odata query to know how that can be done. I would also recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
@@RezaDorrani i have figured out an approach. Odata query has been so big because i was using multiple comboboxes. Instead, i just created a custom collection, created a ForAll in it, and added the current record one by one based on the filtered values. Then i passed the collection to powerautomate, created the schema for it, picked the values and extracted into the excel.
No idea about sql as I have not done any videos on it. I will recommend checking on forums in case someone has done something similar powerusers.microsoft.com
HI Reza, Great video!! quick question...what if, one of the two filters conditions is blank, lets say the dropdown value is null, my get items in flow will return 0 records from the SPList. how to handle this?
hi reza, thanks for the vid. i created the flow, like you, except the style. but i am facing the same problem with an other flow i created like you from an other vid. in my excel there is just one column. all the data are in column A and not separeted in 10 columns ( a column for each field). any recomendations?
I have not come across the mentioned issue in either of the videos showcased and hence not sure what the reason for your specific issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi Reza, your tutorials have been a lifesaver and job keeper for me. We appreciate them very much. I have followed all steps but i still dont get the email. Please assist.
Great to hear! I have not come across any issue with emails not receiving and hence not sure what the cause for your issue could be. I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Hi Reza, Thank you for video. I have one query... If I am reading the data from excel where I have 40,000 rows. Will i be able to read all 40, 000 rows in power automate without using any delegation. And then do some data manipulation and create a new excel and send a mail to respective users(user email is one of the field in the excel)???
This is a really good guide. Only issue that I have is that you should be more careful when creating files on user's One Drive without their consent. I understand you have to create the file in order to convert it to PDF since there is no free built in way to convert HTML to pdf, but creating files and filling user's One Drives with unnecessary files is not the solution.
HI, Reza Wonderful video... I want to export multiple records as well as if they have attachments. I want to include that under each record.. is there any way to do that in flow.. I am confused and new to Flow as well..
I believe Yes but I do not have a video reference on that scenario. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Another wonderful video Reza thank you for this. I created the flow as described. I have a start date picker 'dpstart' and a end date picker 'dpend' in a filter container, both buttons OnChange is set to 'Select(btnLoadData)' and thus when the user selects a date range the btnLoadData button fires and its OnSelect property is set to ClearCollect(colGridData, Filter(TimeLog,Date>=dp start.SelectedDate && Date= dp start.SelectedDate && Date
Most welcome! I have not come across the mentioned issue and hence not sure what the reason for your issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
How could I do if I want to export Specifically selected items from the gallery and not a group filtered by any parameter? I'm thinking of passing the ID of the selected items as an array but then How can I filter in the get items to get only the items with ID inside that array? Kinda solve it already but it's such a pain to deal with multiple choice columns. Is there a simple way of making them appear "normal" in the table?
In this video user can search for Title column in txtsearch input control l. What if we need two additional column like Role and First name. So by adding these column in the Exporttask.Run(..Title,Role,FirstName) Will this work? or Do I need another approach.
Idea is to filter in flow using odata query to ensure maximum performance and data at scale. I have done video on odata queries as well which covers a lot of scenarios.
Hi Reza, Thank you so much, I wanted to ask you one thing is there a way when clicked export to export with out typing the text input at moment it will export blank if you leave blank search text input blank
@@basehumax My comment earlier was the best direction I can give here on chat. If you need the exact code then I would have to try out your scenario to provide it. I will recommend posting your query on the forums at powerusers.microsoft.com
Hi Reza! This works only when the filter is selected. If the filter is not selected, it simply fails. Do we have a solution to tackle this? Thanks! Love your work.
If not filter is selected, then you can handle that logic in code itself. If nothing is selected, then simply pass empty to flow. Check if filter is empty and if yes then accordingly do not apply the filter criteria.
I don’t think there is a direct action for xml. You would need to write expressions and other logic in flow to create the xml file. I have never tried this though. I will recommend checking on forums at powerusers.microsoft.com in case someone has done something similar
Hello Reza, Thank you very much for another excellently done video. As always great explained and step by step approached the solution. I would like to email the contents of a form as a PDF and CSV attachment instead of a gallery (HTML output is not necessary). Do you have a solution for this as well?
Most welcome. A form relates to a single item. Typically an export in csv for 1 item is not an ask. I do not have a video reference for this scenario. Try checking on forums in case someone has done something similar powerusers.microsoft.com
@@RezaDorrani Thanks a lot for your quick response. I am using a galery to filter the form. The form itself is relating to a sharepoint list and includes a lot of fields / informations, so it would be nice to have this as a E-Mail attached CSV file. If you have any solutions due to this export funtionality it would be great. Have a great day ;-)
@@uwecichowicz5444 I would have to try it out to know the solution. Forums would be a good option for you to post or check if anyone has done something similar
Hi reza, the conversion to PDF worked perfectly but I have a good numbers of column hence some data is trimmed a bit. How can I change the orientation of the PDF or some other solution for this?
Hi Reza, Thank you for the informative video. I have a query and that is how this export will work if the gallery items are coming from multiple lists and we want to export the gallery items without delegation limit. Please assist.
Hi Reza! I have a question for you but is not about the video. Recently my boss offered to cover the cost of a Microsoft Security certification course, but I don't know what companies to trust with this. Do you have any suggestions? Thanks!
Its a gallery control that has been styled. I have a video idea planned on showcasing how to style galleries. There was one video I did on this topic a year ago. This would be an extension to that.
@@RezaDorrani I noticed your use of the HTML text control, I've done something similar for some of my apps, I'm really looking forward to your video on this!
quick question - the email is not coming through... wondering if MailTo variable that we setup the top of Power Automate flow needs to be defined at the top, I know when we execute this method we reference the user email. "Export.Run("startswith('***********', '"& lnlTitleSearch_1.Text &"') and ********* eq '"& cboLevel_3.Selected.Value &"'", User().Email);" just wondering what could cause emails to not initiate. thank you, hope you're able to respond!
I have not come across this issue hence not sure what could be the reason for your issue. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I checked power automate log, the process appears to be failing at the point of getting data, doesn't like the Sharepoint site address available in the dropdown as the main option. thanks again! good videos.
Hi Reza, how do you export it if the column is a multi select choice column? Just selecting the column in the Create HTML Table is giving weird results. Thank you
Hi reza - i’m not familiar with the language behind the Run function so i don’t know how i would write “or”s or how to include all values if dpndown is blank, etc. and i have my search field as blank, so i’d like the whole gallery?
@@RezaDorrani anyway I am trying this but it doesn't work😅 Export_task.Run( "startswith(Supplier_code, '" & ib supplier code. Text & '*) and startswith(request_status, '" & dd status. Selected. Value & "*) and Due _date ge '2023-09-11'" ) User () . Email It worked when I did not add date criteria to filter it. I try to filter by date picker and fail so I try to copy query that works on PowerAutomate to PowerApp but still failed.
![ILLSLICK - WINTER IS COMING [Official Video]](http://i.ytimg.com/vi/dwTMeM1zIhQ/mqdefault.jpg)
Finally, a solution to exporting large data. Thank you Reza
You’re most welcome
@ameed I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani top count set to 4999 fixes my issue and allows for more than 5000 items. I award myself 3 Reza points :0)
Reza, Power Apps, Power Automate and SharePoint are my career choice and passion. Because of work related diversions such as meetings, reports, administration and support, I don't have the time to experiment and learn expert level development on my own. You and Shane have filled that requirement over the years to help make me successful. I owe you both a lifetime of gratitude. Thank you!
Reza, thank you for all your input. For me as a non developer all your input puts me in a position to create complex applications in a really short time! Thanks for all your short and concise lesson to this world!
You’re most welcome
गागर में सागर जैसा है आपका ये शार्ट वीडियो
Thank you for being here and helping me in the journey of Power apps and Power automate learning.
शुक्रिया
Your most welcome.
Hello Reza, as always, an awesome video. Every video hold so much knowledge and information; nothing but awesomeness! 👍
Glad to hear that!
Thanks Reza, the interaction between Power Apps and Automate is so much better now as you demonstrated.
Glad to hear that. Thanks for watching.
Thank you so much yet again Reza!
I have had a similar requirement before.
The way I did this was I exported the list using the Export List View feature of SharePoint. Then I uploaded the downloaded IQY file into a SharePoint library. Then in powerapps, I allowed the user to download the iqy file directly from the power app.
Caveats are that the iqy file cannot be used on Mac devices. Also, the custom filter from within powerapps can only be done by creating multiple views in the SharePoint list.
That is also an option. But here it is fully dynamic as the user can pick the filters on the fly.
@@RezaDorrani yes. I love your method for that reason.
I have done this before by sending the JSON to power automate and parsing it but it was slow and hard to maintain. Your method alleviates those issues.
Thanks for the clear explanation. This will be useful for a milage tracking application I've built which is to include a feature of sending a manager a report of the trips taken that are still marked as "Pending" in the SharePoint list. I think I'll include a step that marks the status from Pending to Submitted once the report is sent. As usual, Reza, you have been a wealth of information.
Thanks for watching
Just what I needed. Thanks Reza.👍
Happy to help!
Hi, Reza. Priceless knowledgment. Thanks for sharing !!!
My pleasure!
Thank you really much Ezra for sharing this amazing video. Just a question: how to export data if the gallery filtering is carried out by using two different TextInput boxes?
Whatever filtering logic you have, you would need to recreate in flow. Goal will be to pass the data (multiple params if needed) to flow and then frame your odata query.
Hello Reza, this is gold! thank you for sharing, how would you write a formula if the dropdown is a multi-select combobox?
I would have to try it out to know the formula. I have done video on combo box. Check that one.
Hi Reza, this is a really comprehensive tutorial. Thank you so much.
I would like to ask what would be the formula to get all items if I did not input anything on the dropdown filter? Thank you in advance.
There would be no filter condition in that case in the flow. Pass some param that says all data needed, use it in flow and do not apply any filter condition.
@@RezaDorrani Thanks a lot. But I'm with the same question. If I put some filter in Title but in the drpProgress i don't put anything, it will not return anything as I don't have blank in Progress column. How can I make the sharepoint just look to Title in this cases? You have a video that shows how to filter dropboxes with blank Or(IsBlank(DrpStatus.Selected.Value);Status=DrpStatus.Selected.Value) but doesn't works for sending to Power Automate. Can you help? Thanks in advance.
@@GoncaloMig I would have to recreate your use to case to provide guidance. I would recommend posting your query on forums at powerusers.microsoft.com/
Hi Reza,
Thanks for the video. If you want to export all the data in the list without filtering, what parameter should we write instead of Export.Task.Run(startswith)?
pass empty string as param and try or check in flow if empty string and accordingly do not apply the filter.
Hi Reza, thank you for the video, one question, how would you center HTML table in the email body? Now, table align to left, what if I like table in center of email? Thanks
You would need to add style info to it. I believe only inline styles are supported and very specific ones. You will have to check.
@@RezaDorrani , thank you for your kind info, I will take a look at it now, appreciate it.
Reza, thanks for the explanation 😊
I do have one question, when I get the CSV file from the mail and when it is opened, all the data is in one column separated by commas. How can this is be avoided and ensure the headers are in separate columns.
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thank you Reza, can you assist when using the datepicker filter as query?
Welcome!
If not shown in video then I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza. Thank you for that video. It is always clear and helpful.
May I ask you a question related to the query...
I have a data table (data from a SharePoint list) with multiple dropdwns and search box used to filter.
It is a large query with switch + search + filters (7 dropdowns with IsBlank condistions,...)! Is it possible to copy/paste the query as is and use it in the Run function for the export ? Is there any other actions to perform to pass the query into Power Automate. Thank you for your help.
Idea would be to pass query as is to flow. Remember flow query patter is odata. I have showcased it in video.
@adelinebondjali8116 can you please share your query, have similar requirement
Many thanks Reza very helpful.
I would like to have the pdf file in landscape form, is it possible with power automate?
You would need to use premium connectors to do that
Hi, Reza. Great and helpful video, as always!
For the PDF part, I noticed that the page breaks weren't properly formatted (or probably I'm not used to seeing it). I would like to ask if you have any suggestions/workaround on One Drive converter to PDF having page breaks (and page numbers if possible).
There is no workaround for that page break issue. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza thanks for this great content. I have a question: what is the filtered data is a choice how do we call that in Power Automate and also export function in powerapp?
I have done videos on filter queries in flow where I have covered various column types. I would recommend checking that.
@RezaDorrani Thank you. I will look for it now sir. God bless you.
Hi Reza, thank you for this incredible solution. How do you handle Filters on blank searches? If I do not use search and use a dropdown I return 0 from Sharepoint list. Thank you!
I have not handled that in the video. Idea would be to add blank checks in power apps and accordingly framing the query to pass to flow.
hi Reza, thanks for the video, really its awesome. btw, can we export the sharepoint data without any filters, i mean i wanted to export the entire available data from sharepoint to excel.
Yes, dont pass any filter logic
@@RezaDorrani thanks reza, i have the gallery in my app which is filtered either by Title (or) Client Name (or) Status using 1 textinput box. can we export the filtered data alone to excel? in that case what query we need to use in ExportData.Run(). Currently i use the below formula but flow not running properly it gives error.
ExportData.Run("startswith(Title,'"&TextInput1.Text&"') or startswith(Name of Client,'"&TextInput1.Text&"') or startswith(EM Status,'"&TextInput1.Text&"')",User().Email);
Below is the error message in Flow:
The expression "startswith(Title,'UPS') or startswith(Name of Client,'UPS') or startswith(EM Status,'UPS')" is not valid. Creating query failed.
@@CoolTamilan07 Export Power Apps data to Excel in CSV format
th-cam.com/video/tQCBWMR7T64/w-d-xo.html
Hello Reza, clear instructions thank you . When I enter the formula in the Button it doesn't recognise Startswith . Can you please clarify?
StartsWith is an inbuilt function in Power Apps. Not sure why it would not work for you. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
As per usual, excellent information!! Do you have a video showing how to do this, albeit with small data sets, but group on a lookup field? I seem to recall you having done something like that in past.
th-cam.com/video/tQCBWMR7T64/w-d-xo.html
Thanks for this Reza, I followed your guidance for launching the flow with two queries in my case two choice values, when I try three, I just cannot get it to work. I just added the third the same way I added the second in the video. A bit stumped at the moment.
Depends on how that query is framed. I’ll recommend posting your query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani Thanks for the reply, I figured out why it was not working although still a bit odd. I had to use the display name (internal name) which was this 'Sub_x002d_Category_Choice' instead of 'SubCategory_Choice'. Strange one of the other columns I used was this 'Category_Choice' which is also the Display name. I think I might of changed the column from 'Sub-Category_Choice' to 'SubCategory_Choice' which might have caused this. The code now looks like this:
LifeAfterCollegeExport.Run("startswith(County_Choice, '"& cboMFilterCounty.Selected.Value &"') and Category_Choice eq '"& cboMFilterCategory.Selected.Value &"' and Sub_x002d_Category_Choice eq '"& cboMFilterSubCategory.Selected.Value &"'",User().Email);
Notify("Export process started. You will receive an email once completed.", NotificationType.Success,3000)
Great video again...👍 but I have a question can we export more than one excel csv file?
Pass multiple queries and create multiple csv files.
Thanks, this worked great but one question: One of the fields I am exporting is rich text and I want to remove the html tags when exported to the csv. I have tried multiple methods but to no avail. Do you have any ideas on this one? Any help is greatly appreciated.
Idea would be to look into flow expressions. May be an expression to change html to plain text. I have not tried this so not sure.
Hi Reza
Hope you are well. Thank you for the great content.
Do you have work around in power automate for a SharePoint list that is larger than 5000 records. I am looking for a work around well I am trying to convince my boss to move to dataverse.
Thank you
This video can query large lists as long as the result returned after query is less than 5k records. You can also get more records by turning on pagination for the get items action.
quick question Reza - if I want to add a text field to the page filters (a combo box) but add to the Query dynamically (field is defined as a single line text in SharePoint) how would I write the variable? field_name eq '"& variablename.Text &"' or would it be variablename.SelectedItems.Result or Selected.Value? I have tried various iterations, but power apps seems to be indicating it's an error in the script. Thank you for your video - it's been helpful. AA
Should be combobox.selected.Value or whichever property you want to use.
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thank you my mentor Raz,
At 18:00 how to code for and Status =dropdownlist.selected.value or Status = dropdownlist.selected.value = blank()
I have to dropdown list and the 2nd drop-down is dynamic.
I have done videos on odata filter queries. Check those to see how to frame multiple queries. Alternatively, you can also try posting your query on forums powerusers.microsoft.com
@@RezaDorrani Thank you Rez again. I found a work around by using if function.
Reza, This is great. I had one question. How would one use something other than "startswith" for the export criteria? I currently have a search that uses the "in" function and I'd like to still be able to filter based on that. Is this possible?
Check my video on odata query
I think I understand the power automate Odata query, I meant more about the items in your coding for your export button.
@@nickvargas9597 The video shows sending odata query from apps to flow.
This is perfection, thank you Reza
Thanks so much
Awesome! Excellent walk through.
Thanks for the visit
Love this one Reza...it helps me a lot right now on my project! Only ting I miss is how to get items from multi filter if I dont choose anything from dropdown filter (e.g. I want all items to be returned in Flow)? I'll try to get answer in your another video "OData Filter Query"! Keep up with great job! ♥
Thanks
Hi Reza, great video tutoria!
One question, how can add borders to html table in pdf file? I see in your email attachment you have borders in pdf file.
You would need to add the styling inline.
Some css styling gets stripped in pdf.
@@RezaDorrani do you know any guide to link me?
@@roby9390 I do not have a guide link. I would recommend posting your query on forums at powerusers.microsoft.com
My first time comment to your video. This really helpful. But, how do I pass date to the query. Seem like only text is allowed to be pass in the query.
Check my videos on odata filter query.
You can always pass date as text and use it in queries.
@@RezaDorrani I am happy and thanx for the reply. I watch the videos and identify my error which to be replace >= and
Hi. Great explanation as always. Is it possible to combine several galleries or some other way include a totals box at the bottom? For example, I have several filtered sum columns which I’d like to include at the very bottom of the main data? Many thanks.
Possible.
But I do not have any reference videos on this topic. You would definitely run into delegation warnings.
Check on forums in case someone has done something similar powerusers.microsoft.com
Thank you! For all your helpful content!
Your most welcome
Great explanation. Thank you!!
You are welcome!
Hi Reza, thanks for your help. In my case, I had to use a table instead of a gallery to show the informration as it was a lot. How can it would change as I was trying to use the same formula. thank you in advance.
The technique shown in video depends upon the main query on Items property of table or gallery.
Hi Reza
A small suggestion, it'll be super helpful if the video content also have a blog post (or screenshots of processes ) for a quick reference.
Thanksss a ton!💗💯
Time is limited. Mostly, my videos have links to download the sample apps and flows.
Sure, Reza :)
Hello Reza, Nice video
I was wondering can this been done to generate a csv file instead without using flow and send it with SMTP.SendEmailV3 with this attachment?
Why without flow?
Thanks a lot for all the great videos Reza. Do you have the Github package for this solution ? Thanks again.
I do not have this on GitHub.
Hi, Nice video and great explanation. But i have one question when i am getting more than 5000 records using pagination method in power automate i get error
What error?
You are Awesome👍👃. Can this handle empty, blank and null. As I am using the People Picker ,Datetime and the combo box
Have not tested with blank but should work.
@@RezaDorrani Thank you your reply. If I am not selecting the dropdown or date or any field. I don't want the query to be created for that field is it possible
@@pune2hyd For the query scenario, you would need to write logic in the part where we are passing the query param.
You will need to add blank check in there.
Awesome video Thanks Reza i would like to get all styling from my powerapp while export like an extension to print functionality to export all data to pdf is that possible?
I think u can embed styling to your HTML so that i looks impressive like your powerapp
Not directly as print functionality. You will have to build your own HTML.
👍
Thanks Reza. Your video always help me. Is it possible to get the attachments in each list item and add into CSV to export ?
Possible but I do not have a video reference on it.
Another Great Video. Thanks for sharing and I know I'll come back to it again soon.
Thanks
Thanks Reza!
This works like a charm.
However I have one minor issue, when I'm downloading the CSV File and opening it into Excel, there are quite a few special characters like "’" in some of the cells, along with the information I need.
Not sure about those. Might be some special character related issues. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Thank you, Reza! Great video! In my case, the content of the csv file is in a single column, different from your video... Could you tell me why?
Thanks.
My guess is because something was done right. I will have to look at your flow in action to know the reason for your issue. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Thank Reza, this video can helpfully, I just want to clear my data from gallery is not appear in email but the header can appear in csv and HTML table style? please give me advice
I will have to look at your App in action to provide guidance or reproduce your scenario to know more. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza. This tutorial seems pretty straight forward. I followed it to a T. However, I cannot get the flow to run successfully. It keeps getting stuck on the Get Items. The expression "startswith(Title,'test')" is not valid. Creating query failed. PowerApp button code is ExcelExport.Run("startswith(Title,' "&Searchbox.Text&" ')",User.().Email).
Suggestions?
I have not come across this issue and hence not sure what is the cause for your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Terrific explanation as always. Thanks Reza for covering this scenario.
Your most welcome
Hi Reza,
Thanks for the video
However i was wondering how we are going to achieve this with a combobox(multiselect). How can we write the query for multiselect values?
Should we write for the each value one by one? For ex: by using Last and FirstN function?
But if we have a large number of dropdown values, ex: countries and it is multiselect. Then how should we achieve the filtered excel report?
Thanks again in advance.
I dont think I have a video reference for this scenario. Check documentation on odata query to know how that can be done.
I would also recommend checking or posting your query on forums in case someone has done something similar
powerusers.microsoft.com
@@RezaDorrani i have figured out an approach. Odata query has been so big because i was using multiple comboboxes. Instead, i just created a custom collection, created a ForAll in it, and added the current record one by one based on the filtered values. Then i passed the collection to powerautomate, created the schema for it, picked the values and extracted into the excel.
Thanks for this.
Can we use for data verse?
Can work with any data source.
@@RezaDorrani Thank you for amazing video and reply.
thank you! i was able to link the flow with more than 3 filters, but i was wondering how to do it when there is a date picker as a filter
th-cam.com/video/I8FdUmECAn8/w-d-xo.htmlsi=R_aOF1a1kB7sSx25
Omg, I'm so glad to follow you up, really appreciate your support and videos
Just a wondering, I'm not sure if it's doable, so are we able to do similar filter /flow to be able to download the attachment of the filtered items
@@souheltahhan9585 Probably possible but not something I have tried
Thanks for the video Reza .How can we pass the multi selected combobox selected items in the query for SQL get items ?
No idea about sql as I have not done any videos on it. I will recommend checking on forums in case someone has done something similar
powerusers.microsoft.com
HI Reza, Great video!!
quick question...what if, one of the two filters conditions is blank, lets say the dropdown value is null, my get items in flow will return 0 records from the SPList. how to handle this?
You would need to check for blanks and accordingly manipulate the query being sent to flow.
Thank you so much, Reza, but till now, I am just wondering, why Microsoft had made it hard to have reporting tool for power apps like RDLC in C#!
That’s a question for Microsoft :)
@@RezaDorrani yes indeed 😀
hi reza, thanks for the vid. i created the flow, like you, except the style. but i am facing the same problem with an other flow i created like you from an other vid. in my excel there is just one column. all the data are in column A and not separeted in 10 columns ( a column for each field). any recomendations?
I have not come across the mentioned issue in either of the videos showcased and hence not sure what the reason for your specific issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi Reza, your tutorials have been a lifesaver and job keeper for me. We appreciate them very much. I have followed all steps but i still dont get the email. Please assist.
Great to hear!
I have not come across any issue with emails not receiving and hence not sure what the cause for your issue could be.
I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Hi Reza, Thank you for video. I have one query... If I am reading the data from excel where I have 40,000 rows. Will i be able to read all 40, 000 rows in power automate without using any delegation. And then do some data manipulation and create a new excel and send a mail to respective users(user email is one of the field in the excel)???
I believe Yes.
Check th-cam.com/video/uEZI_b1Gs-k/w-d-xo.html
This is a really good guide. Only issue that I have is that you should be more careful when creating files on user's One Drive without their consent. I understand you have to create the file in order to convert it to PDF since there is no free built in way to convert HTML to pdf, but creating files and filling user's One Drives with unnecessary files is not the solution.
User has to consent to Onedrive action as flow is called from power apps. User has to consent to every connection being used.
HI, Reza Wonderful video... I want to export multiple records as well as if they have attachments. I want to include that under each record..
is there any way to do that in flow.. I am confused and new to Flow as well..
I believe Yes but I do not have a video reference on that scenario.
I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Awesome,thank you so much.
You are Genuis.
You are most welcome
Another wonderful video Reza thank you for this. I created the flow as described. I have a start date picker 'dpstart' and a end date picker 'dpend' in a filter container, both buttons OnChange is set to 'Select(btnLoadData)' and thus when the user selects a date range the btnLoadData button fires and its OnSelect property is set to ClearCollect(colGridData,
Filter(TimeLog,Date>=dp start.SelectedDate && Date= dp start.SelectedDate && Date
Most welcome!
I have not come across the mentioned issue and hence not sure what the reason for your issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
How could I do if I want to export Specifically selected items from the gallery and not a group filtered by any parameter? I'm thinking of passing the ID of the selected items as an array but then How can I filter in the get items to get only the items with ID inside that array?
Kinda solve it already but it's such a pain to deal with multiple choice columns. Is there a simple way of making them appear "normal" in the table?
Probably store selected items in a collection and then use technique shown here th-cam.com/video/tQCBWMR7T64/w-d-xo.htmlsi=fr-CaM5NVmauc0-W
In this video user can search for Title column in txtsearch input control l. What if we need two additional column like Role and First name. So by adding these column in the Exporttask.Run(..Title,Role,FirstName) Will this work? or Do I need another approach.
Key is to frame the query in flow. You can pass values from power apps as you mentioned.
Great Video, I have been looking for Export solution from past few days, looks like you heard it Reza.. :)
Glad to hear that. I did receive a lot of requests on this one.
Nicely explained! how about filtering gallery and exporting the same with ToDate and FromDate with current user
Idea is to filter in flow using odata query to ensure maximum performance and data at scale. I have done video on odata queries as well which covers a lot of scenarios.
Hi Reza, Thank you so much, I wanted to ask you one thing is there a way when clicked export to export with out typing the text input at moment it will export blank if you leave blank search text input blank
For that you will need to update the query param being passed. You can check if the text box is blank and accordingly pass query param.
@@RezaDorrani Give me a direction please which query param to use thanks
@@basehumax My comment earlier was the best direction I can give here on chat. If you need the exact code then I would have to try out your scenario to provide it. I will recommend posting your query on the forums at powerusers.microsoft.com
Thank you so much Reza for wonderful video. Hey, @basehumax I am also finding the solution for same please let me know if you find any solution please
Hi Reza! This works only when the filter is selected. If the filter is not selected, it simply fails. Do we have a solution to tackle this?
Thanks! Love your work.
If not filter is selected, then you can handle that logic in code itself. If nothing is selected, then simply pass empty to flow. Check if filter is empty and if yes then accordingly do not apply the filter criteria.
Very clean solution ... what about exporting to XML instead of CSV/PDF?
I don’t think there is a direct action for xml. You would need to write expressions and other logic in flow to create the xml file.
I have never tried this though. I will recommend checking on forums at powerusers.microsoft.com in case someone has done something similar
Reza, its possible to download file, without receive email, automatically when the user click in export ?
There is a file folder like connector as well for local storage. That could possibly be explored.
Hello Reza,
Thank you very much for another excellently done video. As always great explained and step by step approached the solution.
I would like to email the contents of a form as a PDF and CSV attachment instead of a gallery (HTML output is not necessary). Do you have a solution for this as well?
Most welcome.
A form relates to a single item. Typically an export in csv for 1 item is not an ask.
I do not have a video reference for this scenario. Try checking on forums in case someone has done something similar
powerusers.microsoft.com
@@RezaDorrani Thanks a lot for your quick response. I am using a galery to filter the form. The form itself is relating to a sharepoint list and includes a lot of fields / informations, so it would be nice to have this as a E-Mail attached CSV file. If you have any solutions due to this export funtionality it would be great. Have a great day ;-)
@@uwecichowicz5444 I would have to try it out to know the solution. Forums would be a good option for you to post or check if anyone has done something similar
@@RezaDorrani Great, thank you so much. I am sure you will find a solution ;-)
@@uwecichowicz5444 There is none that I am aware about :)
Hi Raza,
Is there any wat to fetch attachment from SQL table to Power App?
If you know the idea kindly create one video on this.
I have done no work with sql.
Check on forums if someone has done something similar
powerusers.microsoft.com
Hello Reza, thank you very much for this video. Please, can you tell me how you already have csv converted into columns? All the best, Mario
Most welcome. Simply follow the video steps and it should do that automatically. No pre setup required.
Another good one.. Thank you
Thanks
Does Power Automate allow you to format the CSV file such as changing the font for the header and changing the data type in the columns?
I believe No.
Hi reza,
the conversion to PDF worked perfectly but I have a good numbers of column hence some data is trimmed a bit. How can I change the orientation of the PDF or some other solution for this?
I don’t believe any such options are available.
For that you would need to look at 3rd party premium connectors.
Same issue I am facing …any other way to do so..
Hi Reza, Thank you for the informative video. I have a query and that is how this export will work if the gallery items are coming from multiple lists and we want to export the gallery items without delegation limit. Please assist.
In that case, you would need to pass 2 queries to flow. Perform both those queries and merge the result array in flow.
Thank you very much!
Welcome
Hello Reza, for the "MailTo" input, do i neet to set it initially in PowerApps like set(MailTo,User().Email)? please help me here :)
The steps are as shown in video
Hi Reza, thank you. Is it possible to show images in the files?
Not sure
Amazing, very good Reza Dorrani
Thanks
Great video Reza - Thanks for Sharing 🙏
I have a couple of flows that could definitely be cleaned up a bit using this pattern:)
Glad it was helpful!
You are wonderful Reza
Thank You
Hi Reza! I have a question for you but is not about the video. Recently my boss offered to cover the cost of a Microsoft Security certification course, but I don't know what companies to trust with this. Do you have any suggestions? Thanks!
Sorry but I have no clue about this.
Another awesome video, thanks a lot!
Glad you enjoyed it!
Hi Reza
Another good one, thanks very much.
QQ: can you add that HTML table into Excel Table in ".xlsx" like you're doing with CSV?
You can but it is a lot more work. You will need to first create a table in excel, the. Loop through all data and add rows to it.
@@RezaDorrani Yes I learned that from one of your amazing videos.
Was hoping for a quicker solution like adding HTML table to Excel Table...
@@SHAli1474 There is no quicker solution at the moment.
I prefer rest api connector to do it, launch graph excel add row to a template excel in sharepoint.
Thank you again Reza
Most welcome 😊
Dear admin, Please you doing the conditions when select start date & end date and final status when user wants to filter export data. Thank you!
?
Reza, Do you make these cards on Power point?
Its a gallery control that has been styled. I have a video idea planned on showcasing how to style galleries. There was one video I did on this topic a year ago. This would be an extension to that.
@@RezaDorrani I noticed your use of the HTML text control, I've done something similar for some of my apps, I'm really looking forward to your video on this!
quick question - the email is not coming through... wondering if MailTo variable that we setup the top of Power Automate flow needs to be defined at the top, I know when we execute this method we reference the user email. "Export.Run("startswith('***********', '"& lnlTitleSearch_1.Text &"') and ********* eq '"& cboLevel_3.Selected.Value &"'", User().Email);" just wondering what could cause emails to not initiate. thank you, hope you're able to respond!
I have not come across this issue hence not sure what could be the reason for your issue.
I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I checked power automate log, the process appears to be failing at the point of getting data, doesn't like the Sharepoint site address available in the dropdown as the main option. thanks again! good videos.
@@arjumandansari2873 Best to post the issue on the forums as I would need to reproduce your issue to know more.
Hi Reza, how do you export it if the column is a multi select choice column? Just selecting the column in the Create HTML Table is giving weird results. Thank you
Multi select columns will require extra work in flow using expressions to get output in desired format.
Hi reza - i’m not familiar with the language behind the Run function so i don’t know how i would write “or”s or how to include all values if dpndown is blank, etc. and i have my search field as blank, so i’d like the whole gallery?
Thats odata query in flow. Check documentation on odata query syntax. I have also done a video specific to odata filter query.
@@RezaDorrani thank you the quick response reza!
Thanks so much, hope you all the best wish!
Thank you!
@@RezaDorrani anyway I am trying this but it doesn't work😅
Export_task.Run(
"startswith(Supplier_code,
'" & ib supplier code. Text & '*)
and startswith(request_status,
'" & dd status. Selected. Value & "*)
and Due _date ge '2023-09-11'"
)
User () . Email
It worked when I did not add date criteria to filter it.
I try to filter by date picker and fail so I try to copy query that works on PowerAutomate to PowerApp but still failed.
@@RezaDorrani I can solve this finally. Thanks again!
I try to used gallery design like that gallery what kinds of gallery control you used ??
Video on gallery designs th-cam.com/video/bnC8u3gdWss/w-d-xo.html
Hi sir. Grate video. How can we have images in PDF the images coming from attachments?
Check th-cam.com/video/Da4do2DxmJE/w-d-xo.html