@@manasnandi1318 : Yes, it is possible...Just follow the same steps what mentioned by Reza here for "Export to Excel"...instead of sharepoint, just select "OneDrive" and check...Hope it helps...
Dear Reza, my mind has just blown. In a section of this video lasting 5 minutes I found a solution which otherwise would cost many hours or days of struggles. Absolutely brilliant! Thanks so much!
Thanks a lot. I followed your tutorial. For some reason, I wasn't able to create a flow in PowerBI online. Had to do in PowerBI desktop and then publish it to PowerBI online to actually make the button clickable. Your video made it possible to implement this. Thanks.
The performance analyzer is something I wasn't aware of - I had been manually building out my query based on a template. This is so much easier, thanks Reza!
@@RezaDorrani I have some knowledge especially in Power BI, Power Query and Dax; and I've been thinking about start my own channel. Do you have any advice for those who wants to do the same that you do?
@@fidelixwashington Best advice I can give is to start and not think a lot. Initially putting out content that can help solve a problem is the best approach. This has worked for me.
This is awesome. Really helped. The only issue I had in mass export is that it only exported 6K rows and not the 21K that data has. I exported it manually and it has the 21K rows.
Thanks for this video, I tried it, and it works as well. but I have a question -> After exporting large data from this process, if we apply a filter, is there any way to show export data according to its filter.
Reza your content is awesome, i´ve been learning a lot of power apps thanks to you. I have a question regarding Power BI Service and Power Automate, Does exist any posibility to export more than 150k rows? In the example you limit the query to 100k and PBI Service has a default max value for exporting a limited to 150k, i need to increase that number. Thanks in advance :)
Fantastic video Reza! Very informative. I have one question: After running a query against a dataset in PBI, if I want to append those rows on an existing csv file in SharePoint, what would be the best approach?
You would need to leverage actions in flow to do that. I would have to try it out to know what the best approach would be. I recommend checking on forums in case someone has done something similar powerusers.microsoft.com
Since I want to append to an Excel file in SharePoint I will ask the community for Action advice for that result 😀 thanks for posting the link so I can go straight to the community for my questions. You're the best. If I get a response I can post it here to help others
Loving the video, very clear! When creating the CSV i only get the options to work with User ID, User name, User email or timestamp though - no option to work with Power Bi data. Any advice?
I am having the same issue, all the power BI data options are not in the drop down list. I have a feeling it may be to do with power BI premium or power automate premium version?
Hello Reza. I use your video tutorials a lot, you have a very organized and detailed way of presenting taking into consideration many aspects of the topic in question. However in this case I'm not content as this case of extracting data using power automate does not apply when you share a PBI Dash with clients and use Row Level Security. How to overcome that challenge? Please reach out to me so we can discuss a specific case I have. Regards, Kris!
Thanks Kris! There very well may be a limitation with row level security for this. Check documentation on pbi to see if the limit is called out. I would recommend posting your use case on forums in case someone has done something similar powerusers.microsoft.com
What a video!! Had a doubt here , If we apply filters and then run the flow , where the dataset is large, then does the flow give us the filtered csv or does it not? If not, any workarounds?
Great video Mr. Dorrani as usual. I am super new to Power Automate and cant figure out an issue I am having. Using your export to excel process fully, the excel data fields that are dates are not formatted to the same date format in my PBI report. I don't know how to change this in Automate. Do I change this in Automate or is this a PBI issue? Thank you in advance for any guidance you can offer.
Thanks so much. I have not come across this issue hence not sure. I would recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi Reza, Very nice explanation as always. I need to send that particular file to the user who runs that flow. or can i get that file opened in excel web ?
Very informative Reza, just wondering if we can export multiple pages of a power Bi into the excel. that too a dynamic one(excel) with hyperlinks to jump into multiple sheets in excel?
@@ShinChan.unofficial I don’t know if that’s possible. Check or post your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza, awesome video. Just want to ask, what is the equivalent of print of other programming languages in power apps, if I want to see the evaluation of a boolean expression for example
This is a great tutorial, and I've been able to successfully use it. My current dilemma is that I have a Power BI visual with *2* separate reports, side-by-side. I would like to export the PBI sheet with two reports into an Excel that contains both reports. I have an Excel template with two separate defined tables, but I can only seem to export the first report. Is there any way to export both reports into one Excel sheet?
I am not sure if that is a possibility. I will recommend checking or posting your query on forums in case someone has done something similar community.powerplatform.com/
Hello! In this section you demonstrate how to export to CSV, which lines up fairly well with the CSV method earlier in the video: 11:51 - Export large data from Power BI to excel or CSV However - for writing to Excel, the dynamic content for running a query against a dataset does not include the "Power BI Data Item" to add rows into the table as you did earlier in the video. The attempts I have made using the alternative dynamic content options (Row and Body) make for some very long Power Automate Scripts, and they seem to just write a blank table with the same length and width as my query. Would you be able to help me understand how to write to an xlsx file from a dataset query?
@@RezaDorrani Do you have any guide on how to remove the square brackets from the column headers so that they could match an xlsx template with plain text headers?
@@RezaDorrani in case anyone comes through the comments looking for solution - the SELECT function resolved the issue for me as in this video ruclips.net/video/lWu4n-ODe4U/видео.html
WOW Nice, this video is really very helpful, But I need to know how to add a unique number(to get unique rows ) while updating Excel rows from Power BI
Welcome! I do not have a video reference on the scenario mentioned and would have to try it out to know the steps. I suggest posting your query on forums in case someone has done something similar powerusers.microsoft.com/
@@RezaDorrani I saw through few videos but unable to find the right one. In this video a new excel file is created in SharePoint every time the flow runs. Now I want to pick the new file created in SharePoint to be sent through mail. I am not able to do this? Can you please guide or provide the link of your video which I can reference
I found a way from your other videos to send the excel file through mail. But have an issue. There is a date field in my table but when it getting updated in excel file, the date is not in the same format and it is showing a day behind. The date I have in the table is 22-04-2024 but in excel it is uploading as "2024-04-21T18:30:00.000Z"
Possible to run flow on a schedule to run DAX, get data and load in excel. I do not have a video reference on this scenario though and would have to try it out to provide guidance or sequence. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, your video seems very useful. I have similar scenario where I need to export complete data(all columns) from a power bi table which consists around 49K rows and 39 columns, but only 15k records are getting exported into CSV. Request your help on this. Thanks
Limitation is on the size of the file. You may have many columns. Thats why its limiting to 15k in your case. I’m not aware of any option around it other than splitting it into multiple files.
Great video, Reza, thank you so much! This would be great addition to Power BI toolset when you have to limit scope of extracted data and disconnect from dataset ("Analyze in Excel" feature in PBI provides live connection). I have a question regarding RLS (Row Level Security) - for 1000 rows option it's enabled when user logs in into PowerBI, but what about 100k option - I think DAX query is not applying user's profile when querying the dataset and therefore results may include data which is restricted for current user. Is there a way to check this?
Very informative video as always. 🎉 I'm learning a lot from your channel. I have two queries regarding this video. 1. Can this be a workaround for the 5k rows limitation of SharePoint List? 2. Isn't it better to do a "Refresh a dataset" before doing "Run a query against a dataset" so you can download the updated data?
1. I'll be looking forward to that video. 2. I was planning to add this as a download data feature to my Power App so refresh a dataset for every download was ideal for me. Also I prefer this way since SP is not a relational DB, but by using PBI I can combine parent and child db columns before downloading the data.
@@charlesdawnbeltran9603 SharePoint only supports import query. You are limited to max 8 refreshes a day. Your scenarios of power apps refresh would not work beyond 8 refreshes.
Now I know that PBI has only 8 refresh a day. Thanks for this additional info. But do you happen to have a video of left/right joining two SP Lists and create a csv table using Power Automate?
I do not have a video reference for joining 2 SP lists. I would recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com/
Hi Reza Run a query against database is very useful feature. I am using it for around an year. But what I observed is that it gave you data upto 10 MB only. In case returned data is more than 10 Mb flow is failed. Even export paginated report has limit of 100 mb
Limit is 10 MB. Depends on how many rows and columns of data you are trying to retrieve. You can also fire multiple queries one after the other to get data in batches
@@RezaDorrani yes, limit is on data size and not on rows or column and I think it is limit of flow not of BI One more question Reza How can we export data of a matrix in BI. I tried but tabular data as shown in BI can't be exported for a matrix visual
@@DAXifiedSatish Yes its a limit of API. Rows and columns add to the limit. I can get 100k rows with 1 column but may not get the same rows with 50 columns. I am not aware of how to export for matrix visual.
Excellent...Thanks for that video... Everything great, but I have similar use case here...Instead of saving the Excel file onto Ondrive or Sharepoint...Is there any way to save the excel file to local disk of the user who is using and when clicks on "Export to Excel" button...If possible could you please make the video or provide the ref. Thanks in advance.
There is a connector available for that I think, but not something I have tried or have a video reference for. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com/
Great Video, thanks! I have a problem, so when i use the dax query method, i only get the current data i filtered. Now if i want to filter again, i had to adjust the dax query once more in my flow. Is there a way how i can export data with the dynamic filtering?
Im not sure if thats an option. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hello Reza, great video. Thanks a lot. However I am not getting the data into the Excel file. It's just the Excel file is getting created with the table structure but not the content
this is great, thank you for sharing this tip! However, I'm not seeing the button rendering when I publish my powerBI report to the web. It shows up in my powerbi service though. Do you know if there is any workaround? thank you very much
Thank you a Lot Reza. Could you help me with a doubt please? I only can visualize the dashboard in PBI. I want to download and save in onedrive the table that appears in this dashboard automatically. Is there a way to do it? 🙏 When I do it with "run a query against dataset " I can't because I don't have access to this...
hello Reza, I wonder if we used sharepoint list as datasource, is there any limitation for power BI to retrieve data for example 5000 rows ,10000 rows.thanks!
There is a size limit on the exported file. I'm not sure exactly of the file size. Documentation states upto 100k records. Does not matter if SharePoint or Dataverse.
Reza, thanks for the video. I tried the steps using the performance analyzer to extract my DAX. In the apply to each action I selected First table rows for the output from the previous steps and for the Row column in the Add a row into table action I added current item. The flow ran successfully but returned an empty excel file. Please what did I do wrong?
Steps seem fine. Something may not have been done correctly. I would recommend posting your issue with screenshots of query on forums at powerusers.microsoft.com
Reza - one more question for you. It seems that when you copy the query from the Performance Analyzer tool, any DAX measures included in the table are omitted in the CSV output. The CSV output also reformats the column names to the original schema "Table_Name[Column_Name]". Is there a way to include the DAX measures and preserve the column names?
Hi, thank you for sharing, is it possible to extract the large data into Excel through Power Automate Scheduled Flow (without the button on Power BI Report). i am able to get through csv file but not Excel
@@shaibeezz How did you use the Dax Query method to populate the excel template? I know CSV is easier using Map Columns but how did you manage it in excel?
Hi Reza, Thanks a lot for the video. I am working on exporting the data from PowerBI and insert into a SharePoint list. The issue is that if there is a blank column, the flow does not include that row in the SP list. Is there anything that we can do to include null columns row?
Hello Reza, Thanks for sharing the detailed video, this will solve my manual job. I tried to pull dataset after pulling its DAX query, it is pulling half of the records like 2400 records out 5500 records with 106 columns. I modified the Topn as well. What could be the issue in DAX code? Any guidance would be helpful. Thanks again for sharing your knowledge
Thanks for your great video. How do I add a character encoding code? I have Chinese and Japanese and Korean in my data and the csv I get in email is not showing those characters correctly.
Most welcome! I am not aware of a workaround for character encoding. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza, great content again. But in the 3rd part (exporting large data), you have mentioned (@ 14:06 of video) we can export to csv or excel, but only showed csv. How do we export large data to excel. I tried, it's not working.
I mentioned it is possible. I do not have a video reference on it though. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
I need to accept or decline room event on behalf of the meeting room using power automate flow (respond to an event) and my account has a full delegation on all the rooms I cannot respond I got error access denied check your credentials. Is there any work need to be done or any suggestions on how to solve this issue. Thank you so much
Thanks for this! So the max no of rows one may extract is up to 100k (using DAX query extract on power automate). But if I change the top N portion of the code to >100k, will that work to bring in >100k rows?
Thank you for this excellent content. I have one query how we can export matrix data to csv or excel through power automate , Actually I need to go with Date in columns and other item/ product in rows…
Most welcome! I have not tried the matrix data scenario hence not sure. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
@@HarpreetGujral27 I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
@@RezaDorrani the power automate website, I meant. I have managed to generate a .csv dataset from my PBI and add it to an e-mail flow, but I havent figured out a way yet to generate an .xlsx file and send it through an email flow...
Thank you for this video, is there a way to download to the user's local machine (Downloads folder)? I can't use SharePoint or OneDrive for my use case.
I will have to look at your flow in action to know the cause of your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza! Excellent video. One thing I’m grappling with is I’m trying to connect by power app to a csv export from your method but power app doesn’t seem to accept csv as data source. I know it accepts excel table in SharePoint/OneDrive as data source but I can’t figure out how to export array output of ‘running query against dataset’ to excel table. Is this something you can cover? Can you cover how to convert a csv to excel table via power automate cloud flow or convert array output of ‘run query against power bi dataset’ to excel table with power automate cloud?
@@RezaDorrani after 6 hours of trial and error, I finally figured it out. Ended up parsing json output to csv then parsed it to json array again and then converted to excel. Not neat, but it works.
This is fantastic! I modified this flow to attach the .csv to a Gmail email and then send to a distro list. I published the report to the service and one of my developers was unable to use the flow when they clicked the 'run flow' icon. Is there a way to enable other users to click the button and successfully complete the flow?
Hi Reza, is there any steps to be added in the power automate if we only wanted to export the latest data from power bi to sharepoint list on the subsequent flow with large dataset? I’ve implemented the same, however subsequent flow will duplicate all the existing data.
I think I show how to filter power bi dataset with dax query. You can use that logic and filter on whatever criteria defines latest data for your scenario
hi Reza, you da best. I use your same logic but my CSV only exports 9k and the info from the table is 25k, Do you have any idea why this is happening? I really appreciate any help you can provide.
Thanks for the video, I used it to pull Data from Power BI data refresh to sharepoint xlsx. However it is giving me error for apply to each, saying it cannot do more than 5000 rows at a time, is there a workaround for that?
Look into pagination for actions in flow. I do not have a video reference on this scenario though. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Thanks ,Reza. In Desktop PBI , there is an option in PQ whether to run from a sample of the top 1000 rows or from everything. Do you think if I choose everything, I will be able to export large data without having to go through that ?
I have not tried that hence not sure. However, idea is for user to click on a button to perform the export rather than opening report in power query and performing the steps.
This is exactly the question which nobody know the answear ,all tutorials has been tested by small dataset.For example i have sharepoint list included 70 columns, about 36000 items,containig attachments,
@@RezaDorrani thank you,i am still looking for some solution with free license of course🙏they dont want me to get bored😂 at the beginning of learning... the purpose is backuping sharepoint lists.
I have no idea about this issue or it's fix. I will recommend checking or posting your issue on forums in case someone has experienced something similar community.powerplatform.com
@@RezaDorrani Ah ok I get it. 13:30. How can you send/update an excel sheet and have it be updated, rather than making a new file? It would be great if you could make a video where the data is automatically exported to a single excel document then based on one of the cells gets emailed to a set of users. -- Basically alerting users this (project) row is < 7 days from being due. Please update your project status.
@@AmkorTechnology I make videos when I receive multiple requests on a topic. Every quarter I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
I have not come across this issue, hence not sure what the resolution is. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi, Another question: what if I need to export the data in multiple Excel (xlsx) files, e.g. I have sales data by 'Locations' and 'Sales Person,' and I need Flow/PowerAutomate to create a separate file for each Location and Sales Person.
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
@@RezaDorrani@RezaDorrani worked through the following steps. Thank you for being so supportive. 1 - Initialize variable (to define the Array / String to separate file and different file name) 2 - Apply to each - for the value of the Initialize variable 3 - Run a query against a dataset - using "Current Item" of Apply to Each as a data filter 4 - Create a CSV Table (PBI table Rows) 5 - Create File (SharePoint) a - Site Address b - Folder Path c - File name ("Apply to Each Current Item"_utcnow().csv d - File Content (Output of the CSV Table) Works perfectly for CSV, but not for Excel tables same error at "Action 'Add_a_row_into_a_table' failed." A value must be provided for item. clientRequestId: bfdb1c99-7c91-4ba2-80d5-0a40d3f6facb
Is there a way to export a formatted table in Excel.. I have done some RAG background conditional formatting in PBI table, i want to export with the format, is it possible?
Hi @Reza, Could you please suggest how can I transfer my Power bi table data into SQL and that should be automatically going forwards? thanks in advance.
Hello Reza, Can i extract the Matrix table from PBI using Run Query Against dataset, When i tried to do that the DAX only gives the Heading as a table. When i checked the Run in DAX Query View in PBI there are 2 results. the first result only i am getting while extracting. Is there any way to get a complete DAX to extract the Matrix Table?
I have not tried with matrix table - might very well be a limitation of the approach shown in video. I will recommend checking or posting your query on forums in case someone has done something similar community.powerplatform.com/
My whole flow is running successfully but add a row in to a table output is only giving me 2 columns data and rest of the columns header only but no data and that data is a dynamic one that we should be getting in the excel
@rezaDorrani sir Thanks for the video it is very useful and i have one doubt i created the recurrence schedule in power automate for every 4 hours and it is running successfully. but i want to send csv attachment to the my email id whenever new csv file has generated in the folder. I have created email alert in sharepoint i am receiving email for whenever new file has created in the documents folder im getting email but i am not getting the csv attachment . Please help me how to achieve that. thanks a lot. and also in my power bi table visual have % fields/columns also but in csv it is coming like decimal numbers how to solve this please help. Thanks again..
I have not come across this issue and hence not sure what the cause could be. I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Wooow! Super useful video. Thank you a lot!
Most welcome! Thanks for watching
@@RezaDorranican you export excel through one drive?
@@manasnandi1318 should be possible but not something I have tried
@@manasnandi1318 : Yes, it is possible...Just follow the same steps what mentioned by Reza here for "Export to Excel"...instead of sharepoint, just select "OneDrive" and check...Hope it helps...
Dear Reza, my mind has just blown. In a section of this video lasting 5 minutes I found a solution which otherwise would cost many hours or days of struggles. Absolutely brilliant! Thanks so much!
Excellent! Thanks so much for watching
Thanks a lot. I followed your tutorial. For some reason, I wasn't able to create a flow in PowerBI online. Had to do in PowerBI desktop and then publish it to PowerBI online to actually make the button clickable. Your video made it possible to implement this. Thanks.
Most welcome
This is THE video of our PowerBI times... We are served by brilliance with magnificence. Thank you!!!
Wow! That’s a big compliment. Thank You
The performance analyzer is something I wasn't aware of - I had been manually building out my query based on a template. This is so much easier, thanks Reza!
Most welcome! Performance analyzer is awesome
This is amazing! You never fail me, Reza! You just saved me weeks of work.
I'm so glad!
Game changer for me!!! thank you - just need to figure now how to send the exported csv to different locations based on the user running the flow :)
Glad it helped!
Another masterpiece! Thanks Reza for all that knowledge you've been sharing with us. I've learned a lot with from your channel!
Thanks so much for watching the videos
@@RezaDorrani I have some knowledge especially in Power BI, Power Query and Dax; and I've been thinking about start my own channel. Do you have any advice for those who wants to do the same that you do?
@@fidelixwashington Best advice I can give is to start and not think a lot. Initially putting out content that can help solve a problem is the best approach. This has worked for me.
@@RezaDorrani thank you again!
This is awesome. Really helped. The only issue I had in mass export is that it only exported 6K rows and not the 21K that data has. I exported it manually and it has the 21K rows.
It has a size limit in exporting data. Number of columns also plays an important role. I believe I did call out size limit in video.
@@RezaDorrani
Is there any way to bypass this limit?
@@yan-ct7it No
This was a great video Reza!! So smooth and easy to understand, and very much of use!! Thanks!
Great to hear!
@@RezaDorrani One question, do you have any video where you export it per Teams chat in some HTML formatted table?
@@mirrrvelll5164 I do not have a video reference for that
Litterly this was my user requirement. Thanks Reza for sharing wonderful content. Always referred your channel to others :)
Thanks so much for the referrals
Superb content Reza ! was unaware of incredible features available for PBI in power automate
Glad it was helpful!
Great Latest Update Lesson! It can benefit any organizations.
Glad to hear that
Thanks!
Thank You!
Good video. It helped me a lot to understand some aspects of Power Automate!
Thank you very much, Reza
Glad it helped!
Thanks for this video, I tried it, and it works as well. but I have a question -> After exporting large data from this process, if we apply a filter, is there any way to show export data according to its filter.
It should but not something I have tried
Reza - as always, wonderful knowledge and information! Thank you for sharing, as always!
Most welcome Manny
Very helpful content explained in a very simple and easy manner. Thank you
You are most welcome!
Thank you! Excellent video that completely solve my problem
Glad to hear that!
Reza your content is awesome, i´ve been learning a lot of power apps thanks to you.
I have a question regarding Power BI Service and Power Automate, Does exist any posibility to export more than 150k rows? In the example you limit the query to 100k and PBI Service has a default max value for exporting a limited to 150k, i need to increase that number.
Thanks in advance :)
I dont think we can increase that limit.
Once again a super useful video 👍thanks a lot😊
Glad to hear that! Thanks so much for watching.
Really useful video. I can think of few ways to apply it. Thanks Reza
Most welcome
Fantastic video Reza! Very informative.
I have one question: After running a query against a dataset in PBI, if I want to append those rows on an existing csv file in SharePoint, what would be the best approach?
You would need to leverage actions in flow to do that. I would have to try it out to know what the best approach would be.
I recommend checking on forums in case someone has done something similar
powerusers.microsoft.com
Since I want to append to an Excel file in SharePoint I will ask the community for Action advice for that result 😀 thanks for posting the link so I can go straight to the community for my questions. You're the best. If I get a response I can post it here to help others
Loving the video, very clear! When creating the CSV i only get the options to work with User ID, User name, User email or timestamp though - no option to work with Power Bi data. Any advice?
Steps are all in video. I have not come across issue where I have not got data from power bi, hence I am not sure what reason for your issue could be.
I am having the same issue, all the power BI data options are not in the drop down list. I have a feeling it may be to do with power BI premium or power automate premium version?
@@davidlinx5 Drag the fields into the step Power Automate, that's probably what you forgot to do.
Same problem
Amazing , thanks, and just to add and ask we need to have separate licenses too power bi and flows ?
Yes, licenses are separate
Hello Reza. I use your video tutorials a lot, you have a very organized and detailed way of presenting taking into consideration many aspects of the topic in question. However in this case I'm not content as this case of extracting data using power automate does not apply when you share a PBI Dash with clients and use Row Level Security. How to overcome that challenge? Please reach out to me so we can discuss a specific case I have. Regards, Kris!
Thanks Kris!
There very well may be a limitation with row level security for this. Check documentation on pbi to see if the limit is called out.
I would recommend posting your use case on forums in case someone has done something similar powerusers.microsoft.com
What a video!!
Had a doubt here , If we apply filters and then run the flow , where the dataset is large, then does the flow give us the filtered csv or does it not? If not, any workarounds?
I show 2 techniques in video. Check the one using DAX
Thanks a lot.Super.Save my work.
Most welcome
Good Video helped me a lot!! One thing that this also has 100k limitation. What if have to export more than 100k?
Im not aware of an option to load more than 100k
Amazing! Thank you so much for sharing!
Most welcome!
Really Awesome! Thank you.
You're very welcome!
Thanks Reza. Great work as usual
Thanks Luis
Great video Mr. Dorrani as usual. I am super new to Power Automate and cant figure out an issue I am having. Using your export to excel process fully, the excel data fields that are dates are not formatted to the same date format in my PBI report. I don't know how to change this in Automate. Do I change this in Automate or is this a PBI issue? Thank you in advance for any guidance you can offer.
Thanks so much.
I have not come across this issue hence not sure. I would recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
It is just awesome, thank you so much!!!
You're most welcome!
Great content! will try to apply in the next few days as I have a similar task. Thank you!
Best of luck!
Hi Reza,
Very nice explanation as always.
I need to send that particular file to the user who runs that flow. or can i get that file opened in excel web ?
When flow is triggered from power bi, you get that users email as dynamic content.
@@RezaDorrani Okay, Thanks Reza
Very nice, so helpful!! Thanks!!!
You're welcome!
Hello, thanks for the video and explanation. Does it also work with large amounts and more than 500k rows?
1st technique: 2k rows
2nd technique: has a file size limit. Check documentation. Depends upon combination of rows and columns.
what is the limit of the size?@@RezaDorrani
@@TK-pc3rw I don’t remember. You will need to check in the documentation.
Very informative Reza, just wondering if we can export multiple pages of a power Bi into the excel. that too a dynamic one(excel) with hyperlinks to jump into multiple sheets in excel?
I’m not sure if that is possible.
@@RezaDorrani Thanks for replying, i was wondering of it had the option like we used to get in sas eg
@@ShinChan.unofficial I don’t know if that’s possible. Check or post your query on forums in case someone has done something similar
powerusers.microsoft.com
Thank you a lot! Super useful!
Glad to hear that!
Hi Reza, awesome video. Just want to ask, what is the equivalent of print of other programming languages in power apps, if I want to see the evaluation of a boolean expression for example
You can add a label to show its evaluation or simply select the formula in app edit mode.
Very helpful! what if i wanted to do it periodically every morning isntead of when a power bi button is clicked?
The second part of the video is one that can be used in a scheduled flow.
This is a great tutorial, and I've been able to successfully use it. My current dilemma is that I have a Power BI visual with *2* separate reports, side-by-side. I would like to export the PBI sheet with two reports into an Excel that contains both reports. I have an Excel template with two separate defined tables, but I can only seem to export the first report. Is there any way to export both reports into one Excel sheet?
I am not sure if that is a possibility.
I will recommend checking or posting your query on forums in case someone has done something similar
community.powerplatform.com/
Amazing helpful video!!
Great video. Thank you Reza
Most welcome
Hello!
In this section you demonstrate how to export to CSV, which lines up fairly well with the CSV method earlier in the video:
11:51 - Export large data from Power BI to excel or CSV
However - for writing to Excel, the dynamic content for running a query against a dataset does not include the "Power BI Data Item" to add rows into the table as you did earlier in the video. The attempts I have made using the alternative dynamic content options (Row and Body) make for some very long Power Automate Scripts, and they seem to just write a blank table with the same length and width as my query.
Would you be able to help me understand how to write to an xlsx file from a dataset query?
I have done videos on adding data to excel files. I would recommend checking those. You would need to merge logic in this video with that one.
@@RezaDorrani Do you have any guide on how to remove the square brackets from the column headers so that they could match an xlsx template with plain text headers?
@@fullmetalsweater1 I do not have any video references on it. Would have to try it out to know the steps.
@@RezaDorrani in case anyone comes through the comments looking for solution - the SELECT function resolved the issue for me as in this video ruclips.net/video/lWu4n-ODe4U/видео.html
WOW Nice, this video is really very helpful, But I need to know how to add a unique number(to get unique rows ) while updating Excel rows from Power BI
Welcome!
I do not have a video reference on the scenario mentioned and would have to try it out to know the steps.
I suggest posting your query on forums in case someone has done something similar
powerusers.microsoft.com/
Excellent! Thank you!
Most welcome
This was excellent and I have got the answer I required. Can you please advise how to send the excel file created in mail attachment?
Welcome!
I have done videos on sending emails with attachments. I would recommend checking my Power Automate playlist.
@@RezaDorrani I saw through few videos but unable to find the right one. In this video a new excel file is created in SharePoint every time the flow runs. Now I want to pick the new file created in SharePoint to be sent through mail. I am not able to do this? Can you please guide or provide the link of your video which I can reference
@@AnandR-tp9oj I would recommend checking or posting your query on forums
powerusers.microsoft.com
@@RezaDorrani thank you
I found a way from your other videos to send the excel file through mail. But have an issue. There is a date field in my table but when it getting updated in excel file, the date is not in the same format and it is showing a day behind. The date I have in the table is 22-04-2024 but in excel it is uploading as "2024-04-21T18:30:00.000Z"
Great video! Is it possible to run a recurring dax query where the flow overwrites an excel file? If yes, which sequence?
Possible to run flow on a schedule to run DAX, get data and load in excel. I do not have a video reference on this scenario though and would have to try it out to provide guidance or sequence. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, your video seems very useful. I have similar scenario where I need to export complete data(all columns) from a power bi table which consists around 49K rows and 39 columns, but only 15k records are getting exported into CSV. Request your help on this. Thanks
Limitation is on the size of the file. You may have many columns. Thats why its limiting to 15k in your case. I’m not aware of any option around it other than splitting it into multiple files.
Great video, Reza, thank you so much! This would be great addition to Power BI toolset when you have to limit scope of extracted data and disconnect from dataset ("Analyze in Excel" feature in PBI provides live connection). I have a question regarding RLS (Row Level Security) - for 1000 rows option it's enabled when user logs in into PowerBI, but what about 100k option - I think DAX query is not applying user's profile when querying the dataset and therefore results may include data which is restricted for current user. Is there a way to check this?
I am not well versed with RLS so not sure about that. May be a good idea to check on the forums at powerusers.microsoft.com
Hi Buddy can you help me to save that csv file to my local pc instead of sharepoint folder.
Very informative video as always. 🎉 I'm learning a lot from your channel.
I have two queries regarding this video.
1. Can this be a workaround for the 5k rows limitation of SharePoint List?
2. Isn't it better to do a "Refresh a dataset" before doing "Run a query against a dataset" so you can download the updated data?
1 - No.
I have a video coming on searching SP lists.
2 - Yes, depends upon your use case. What if I have a schedule refresh for data set?
1. I'll be looking forward to that video.
2. I was planning to add this as a download data feature to my Power App so refresh a dataset for every download was ideal for me. Also I prefer this way since SP is not a relational DB, but by using PBI I can combine parent and child db columns before downloading the data.
@@charlesdawnbeltran9603 SharePoint only supports import query. You are limited to max 8 refreshes a day. Your scenarios of power apps refresh would not work beyond 8 refreshes.
Now I know that PBI has only 8 refresh a day. Thanks for this additional info.
But do you happen to have a video of left/right joining two SP Lists and create a csv table using Power Automate?
I do not have a video reference for joining 2 SP lists. I would recommend checking or posting your query on forums in case someone has done something similar
powerusers.microsoft.com/
Hi Reza,
Thanks for this great video!
Do you know how I can schedule power bi into a template? The option only seems to work for CSV files.
I have showcased all the options I am aware of in video. I will recommend checking or posting your query on forums at powerusers.microsoft.com
very helpful
Glad to hear that
@RezaDorrani Thank you so much for such an informative video. Is it also possible to schedule the export instead of doing it through a button?
The last part of the video where dax is involved. That can be a scheduled flow.
Hi Reza
Run a query against database is very useful feature. I am using it for around an year. But what I observed is that it gave you data upto 10 MB only. In case returned data is more than 10 Mb flow is failed.
Even export paginated report has limit of 100 mb
Limit is 10 MB. Depends on how many rows and columns of data you are trying to retrieve. You can also fire multiple queries one after the other to get data in batches
@@RezaDorrani yes, limit is on data size and not on rows or column and I think it is limit of flow not of BI
One more question Reza How can we export data of a matrix in BI. I tried but tabular data as shown in BI can't be exported for a matrix visual
@@DAXifiedSatish Yes its a limit of API. Rows and columns add to the limit.
I can get 100k rows with 1 column but may not get the same rows with 50 columns.
I am not aware of how to export for matrix visual.
@@RezaDorrani thanks Reza for your reply
@@DAXifiedSatish dude if you got the solution can you guide me some how
Excellent...Thanks for that video...
Everything great, but I have similar use case here...Instead of saving the Excel file onto Ondrive or Sharepoint...Is there any way to save the excel file to local disk of the user who is using and when clicks on "Export to Excel" button...If possible could you please make the video or provide the ref. Thanks in advance.
There is a connector available for that I think, but not something I have tried or have a video reference for.
I will recommend checking or posting your query on forums in case someone has done something similar
powerusers.microsoft.com/
Great Video, thanks!
I have a problem, so when i use the dax query method, i only get the current data i filtered. Now if i want to filter again, i had to adjust the dax query once more in my flow. Is there a way how i can export data with the dynamic filtering?
Im not sure if thats an option.
I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hello Reza, great video. Thanks a lot.
However I am not getting the data into the Excel file. It's just the Excel file is getting created with the table structure but not the content
I have not come across this issue hence not sure. I would recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
same Problem
@@hrithik-sharma Forums will be your best bet
this is great, thank you for sharing this tip! However, I'm not seeing the button rendering when I publish my powerBI report to the web. It shows up in my powerbi service though. Do you know if there is any workaround? thank you very much
I have not experienced this issue, hence not sure
@@RezaDorrani no worries, I'll dig a bit more, thank you Reza!
Thanks!! if I do refresh query in the excel table it will update with latest in PBI dashboard?
No - you will need to refresh power bi dataset
Thank you a Lot Reza.
Could you help me with a doubt please?
I only can visualize the dashboard in PBI. I want to download and save in onedrive the table that appears in this dashboard automatically. Is there a way to do it? 🙏
When I do it with "run a query against dataset " I can't because I don't have access to this...
I believe you would need access to export from flow.
hello Reza, I wonder if we used sharepoint list as datasource, is there any limitation for power BI to retrieve data for example 5000 rows ,10000 rows.thanks!
There is a size limit on the exported file. I'm not sure exactly of the file size. Documentation states upto 100k records. Does not matter if SharePoint or Dataverse.
Reza, thanks for the video.
I tried the steps using the performance analyzer to extract my DAX. In the apply to each action I selected First table rows for the output from the previous steps and for the Row column in the Add a row into table action I added current item. The flow ran successfully but returned an empty excel file. Please what did I do wrong?
Steps seem fine. Something may not have been done correctly. I would recommend posting your issue with screenshots of query on forums at powerusers.microsoft.com
@@RezaDorrani Thank you.
Reza - one more question for you. It seems that when you copy the query from the Performance Analyzer tool, any DAX measures included in the table are omitted in the CSV output. The CSV output also reformats the column names to the original schema "Table_Name[Column_Name]". Is there a way to include the DAX measures and preserve the column names?
I don’t know of a way to include DAX measures so not sure.
Column names can be renamed using flow expressions
Hi,
thank you for sharing, is it possible to extract the large data into Excel through Power Automate Scheduled Flow (without the button on Power BI Report). i am able to get through csv file but not Excel
Worked - thank you
Covered in video :)
@@shaibeezz How did you use the Dax Query method to populate the excel template? I know CSV is easier using Map Columns but how did you manage it in excel?
Hi Reza, Thanks a lot for the video. I am working on exporting the data from PowerBI and insert into a SharePoint list. The issue is that if there is a blank column, the flow does not include that row in the SP list. Is there anything that we can do to include null columns row?
Thanks!
Not sure about that.
Hello Reza,
Thanks for sharing the detailed video, this will solve my manual job. I tried to pull dataset after pulling its DAX query, it is pulling half of the records like 2400 records out 5500 records with 106 columns. I modified the Topn as well. What could be the issue in DAX code? Any guidance would be helpful.
Thanks again for sharing your knowledge
If depends on the size. Not on the rows. Too many columns would reduce row size.
@@RezaDorrani Thanks a lot for responding
Thanks for your great video. How do I add a character encoding code? I have Chinese and Japanese and Korean in my data and the csv I get in email is not showing those characters correctly.
Most welcome!
I am not aware of a workaround for character encoding. I recommend checking or posting your query on forums in case someone has done something similar
powerusers.microsoft.com
Hi Reza, great content again. But in the 3rd part (exporting large data), you have mentioned (@ 14:06 of video) we can export to csv or excel, but only showed csv. How do we export large data to excel. I tried, it's not working.
I mentioned it is possible. I do not have a video reference on it though. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Thank you a lot very useful .
Dose this work if I need to
Export power bi data to pdf?
Once you have the data in flow, you can use to perform actions of your choice.
Thank you for your quick response
Could I ask you a question which is not related to this topic plz?
@@rayanmohammad910 I can guide or help depending upon wether I have done something related to your question
I need to accept or decline room event on behalf of the meeting room using power automate flow (respond to an event) and my
account has a full delegation on all the rooms
I cannot respond I got error access denied check your credentials.
Is there any work need to be done or any suggestions on how to solve this issue.
Thank you so much
Thanks for this! So the max no of rows one may extract is up to 100k (using DAX query extract on power automate). But if I change the top N portion of the code to >100k, will that work to bring in >100k rows?
I dont remember the exact value. Its more on the size of the extracted table. More columns would be less number of rows.
Thank you for this excellent content. I have one query how we can export matrix data to csv or excel through power automate , Actually I need to go with Date in columns and other item/ product in rows…
Most welcome!
I have not tried the matrix data scenario hence not sure. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Yeah, I checked there as well but all similar queries are unanswered. It would be great if you can prepare similar videos on this!
@@HarpreetGujral27 I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
@@RezaDorrani sure Thank You !
Very nice! Thanks, mate!
Most welcome! Thanks so much for watching
Thank you for the video, very useful. Is there a way I could also send an e-mail with that generated .xlsx file to specific e-mails?
PBI does not have am option with the flow visual to send an email value while calling the flow
@@RezaDorrani it wouldnt have to be through the visual, it could be through the website.
@@jntlvr2898 which website?
@@RezaDorrani the power automate website, I meant.
I have managed to generate a .csv dataset from my PBI and add it to an e-mail flow, but I havent figured out a way yet to generate an .xlsx file and send it through an email flow...
@@jntlvr2898 I am not sure if there is an option
Thank you for this video, is there a way to download to the user's local machine (Downloads folder)? I can't use SharePoint or OneDrive for my use case.
powerautomate.microsoft.com/en-us/connectors/details/shared_filesystem/file-system/
Hi Reza. Gr8!vdo. I tried the flow and it works but no data gets exported into excel. What can i be missing?
I will have to look at your flow in action to know the cause of your issue. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
I noticed that the export was being done but from row 10147. That is why i couldnt see it. Not sure why export began in row 10147…
@@wodnyaquarius849 I have no idea.
Hi Reza! Excellent video. One thing I’m grappling with is I’m trying to connect by power app to a csv export from your method but power app doesn’t seem to accept csv as data source. I know it accepts excel table in SharePoint/OneDrive as data source but I can’t figure out how to export array output of ‘running query against dataset’ to excel table. Is this something you can cover? Can you cover how to convert a csv to excel table via power automate cloud flow or convert array output of ‘run query against power bi dataset’ to excel table with power automate cloud?
I don’t know if that is possible. I’ll recommend checking on forums in case someone has done something similar
powerusers.microsoft.com
@@RezaDorrani after 6 hours of trial and error, I finally figured it out. Ended up parsing json output to csv then parsed it to json array again and then converted to excel. Not neat, but it works.
This is fantastic! I modified this flow to attach the .csv to a Gmail email and then send to a distro list. I published the report to the service and one of my developers was unable to use the flow when they clicked the 'run flow' icon. Is there a way to enable other users to click the button and successfully complete the flow?
You have to give users run only permissions on the flow
@@RezaDorrani Thanks!! Works perfectly now!
Hi Reza, is there any steps to be added in the power automate if we only wanted to export the latest data from power bi to sharepoint list on the subsequent flow with large dataset? I’ve implemented the same, however subsequent flow will duplicate all the existing data.
I think I show how to filter power bi dataset with dax query. You can use that logic and filter on whatever criteria defines latest data for your scenario
hi Reza, you da best. I use your same logic but my CSV only exports 9k and the info from the table is 25k, Do you have any idea why this is happening? I really appreciate any help you can provide.
Limit is on the size of the file exported not specifically on number of rows. You probably have a lot more columns.
Hi Reza
Is it possible to take the transformed table itself instead of selecting fields because additional column will be added later.
You have to pass fields to flow.
Thanks for the video, I used it to pull Data from Power BI data refresh to sharepoint xlsx. However it is giving me error for apply to each, saying it cannot do more than 5000 rows at a time, is there a workaround for that?
Look into pagination for actions in flow. I do not have a video reference on this scenario though. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
very useful, but is there a way I could download the complete Matrix uisng PA.
I’m not sure if that’s an option
Thanks ,Reza. In Desktop PBI , there is an option in PQ whether to run from a sample of the top 1000 rows or from everything. Do you think if I choose everything, I will be able to export large data without having to go through that ?
I have not tried that hence not sure. However, idea is for user to click on a button to perform the export rather than opening report in power query and performing the steps.
This is exactly the question which nobody know the answear ,all tutorials has been tested by small dataset.For example i have sharepoint list included 70 columns, about 36000 items,containig attachments,
@@Jana-sc7oh I am not aware of a fix for this
@@RezaDorrani thank you,i am still looking for some solution with free license of course🙏they dont want me to get bored😂 at the beginning of learning...
the purpose is backuping sharepoint lists.
@@Jana-sc7oh Ok
i have one query sir can we export the power bi report completely as it is into Excel or not or just by visual and visuals?
I’m not sure if you can export as is with visuals
Wonderful video.Tthanks. When I export the data to csv,the data that involves Chinese,the exported data will be garbled.How can I fix it?
I have no idea about this issue or it's fix.
I will recommend checking or posting your issue on forums in case someone has experienced something similar
community.powerplatform.com
Hi Reza will the large data export work on extracting 2 M - 4M records?
It wont as there is a limit
How do you do it so it run an a schedule? How do you tell PA which Power BI graph/table to reference?
2 variations are covered in video. Flow triggered from power apps and flows triggered manually (which can be run on a schedule).
@@RezaDorrani Ah ok I get it. 13:30.
How can you send/update an excel sheet and have it be updated, rather than making a new file?
It would be great if you could make a video where the data is automatically exported to a single excel document then based on one of the cells gets emailed to a set of users.
-- Basically alerting users this (project) row is < 7 days from being due. Please update your project status.
@@AmkorTechnology I make videos when I receive multiple requests on a topic. Every quarter I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
What is i want the user to download instead of storing it in Sharepoint or any shared directory ?
I dont think flow has a download function
Great
Thanks
Thanks for the video, but when I schedule my report for refresh, the .csv file is not getting updated, please advice
I have not come across this issue, hence not sure what the resolution is. I recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Holy crap that is clever!
👍
Hi,
Another question: what if I need to export the data in multiple Excel (xlsx) files, e.g.
I have sales data by 'Locations' and 'Sales Person,' and I need Flow/PowerAutomate to create a separate file for each Location and Sales Person.
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
@@RezaDorrani@RezaDorrani, thank you very much. I will wait for your next video.
@@RezaDorrani@RezaDorrani worked through the following steps. Thank you for being so supportive.
1 - Initialize variable (to define the Array / String to separate file and different file name)
2 - Apply to each - for the value of the Initialize variable
3 - Run a query against a dataset - using "Current Item" of Apply to Each as a data filter
4 - Create a CSV Table (PBI table Rows)
5 - Create File (SharePoint)
a - Site Address
b - Folder Path
c - File name ("Apply to Each Current Item"_utcnow().csv
d - File Content (Output of the CSV Table)
Works perfectly for CSV, but not for Excel tables same error at "Action 'Add_a_row_into_a_table' failed."
A value must be provided for item.
clientRequestId: bfdb1c99-7c91-4ba2-80d5-0a40d3f6facb
Is there a way to export a formatted table in Excel.. I have done some RAG background conditional formatting in PBI table, i want to export with the format, is it possible?
I am not aware of an option to do that
Hi @Reza, Could you please suggest how can I transfer my Power bi table data into SQL and that should be automatically going forwards? thanks in advance.
No idea about that one. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hello Reza, Can i extract the Matrix table from PBI using Run Query Against dataset, When i tried to do that the DAX only gives the Heading as a table. When i checked the Run in DAX Query View in PBI there are 2 results. the first result only i am getting while extracting. Is there any way to get a complete DAX to extract the Matrix Table?
I have not tried with matrix table - might very well be a limitation of the approach shown in video.
I will recommend checking or posting your query on forums in case someone has done something similar
community.powerplatform.com/
My whole flow is running successfully but add a row in to a table output is only giving me 2 columns data and rest of the columns header only but no data and that data is a dynamic one that we should be getting in the excel
I have not experienced this issue, hence not sure
@rezaDorrani sir Thanks for the video it is very useful and i have one doubt i created the recurrence schedule in power automate for every 4 hours and it is running successfully. but i want to send csv attachment to the my email id whenever new csv file has generated in the folder. I have created email alert in sharepoint i am receiving email for whenever new file has created in the documents folder im getting email but i am not getting the csv attachment . Please help me how to achieve that. thanks a lot. and also in my power bi table visual have % fields/columns also but in csv it is coming like decimal numbers how to solve this please help. Thanks again..
I have not come across this issue and hence not sure what the cause could be.
I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com