How to Export Power BI Data to Excel | Query against a dataset

Рет қаралды 90,063

Күн бұрын

Do you want to export your Power BI report data to Excel or CSV file?
In this video, we will explore multiple options on export to excel from Power BI data with a button.
We will take advantage of the Power Automate Visual in Power BI to send data to flow, flow can create the CSV or Excel file and export it to a data source of our choice like SharePoint. I will also showcase how to directly perform a query against a Power BI dataset in Power Automate flow, which directly performs a Power BI REST API to run the query to get large data (There is a limit of 100,000 rows on this API).
The Power Automate flow visual can export your data to excel using a button in Power BI but there is a 1000 row limit. If using the "perform a query against a Power BI dataset" flow action, you can export up to 100,000 rows per query!
I will also showcase a simple way to get DAX Query using the Performance Analyzer feature in Power BI desktop.
#PowerBI #Excel #powerbidesktop #powerautomate
Table of Contents:
00:00 - Introduction on How to Export Power BI Data to Excel or CSV
00:36 - Power BI Report - Export Data button using Power Automate flow Visual
02:06 - Export to CSV button flow from Power BI Report
05:53 - How to export Power BI data to an Excel file
10:41 - Share the export to Power BI flows
11:16 - 1000 row limit with Power Automate flow Visual in Power BI
11:51 - Export large data from Power BI to excel or CSV
15:20 - Run export excel flow from Power BI based on many trigger options
15:52 - Run a dynamic DAX query against a Power BI dataset in Power Automate flows
17:50 - Subscribe to Reza Dorrani channel
🤝 Let’s connect on social:
🔗 Reza Dorrani LinkedIn: / rezadorrani
🐦 Reza Dorrani Twitter: / rezadorrani

Пікірлер: 337

@VlasiychukY Жыл бұрын

Wooow! Super useful video. Thank you a lot!

@RezaDorrani Жыл бұрын

Most welcome! Thanks for watching

@stephendemanovich3974 Жыл бұрын

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 Жыл бұрын

Most welcome! Performance analyzer is awesome

@UlyssesHaq 9 ай бұрын

This is THE video of our PowerBI times... We are served by brilliance with magnificence. Thank you!!!

@RezaDorrani 9 ай бұрын

Wow! That’s a big compliment. Thank You

@tomoleusz 10 ай бұрын

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!

@RezaDorrani 10 ай бұрын

Excellent! Thanks so much for watching

@krmason 3 ай бұрын

This is amazing! You never fail me, Reza! You just saved me weeks of work.

@RezaDorrani 3 ай бұрын

I'm so glad!

@fidelixwashington Жыл бұрын

Another masterpiece! Thanks Reza for all that knowledge you've been sharing with us. I've learned a lot with from your channel!

@RezaDorrani Жыл бұрын

Thanks so much for watching the videos

@fidelixwashington Жыл бұрын

@@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?

@RezaDorrani Жыл бұрын

@@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.

@fidelixwashington Жыл бұрын

@@RezaDorrani thank you again!

@mirrrvelll5164 13 күн бұрын

This was a great video Reza!! So smooth and easy to understand, and very much of use!! Thanks!

@RezaDorrani 13 күн бұрын

Great to hear!

@mirrrvelll5164 7 күн бұрын

@@RezaDorrani One question, do you have any video where you export it per Teams chat in some HTML formatted table?

@RezaDorrani 7 күн бұрын

@@mirrrvelll5164 I do not have a video reference for that

@mannymorales7913 Жыл бұрын

Reza - as always, wonderful knowledge and information! Thank you for sharing, as always!

@RezaDorrani Жыл бұрын

Most welcome Manny

@dsars79 Жыл бұрын

Litterly this was my user requirement. Thanks Reza for sharing wonderful content. Always referred your channel to others :)

@RezaDorrani Жыл бұрын

Thanks so much for the referrals

@OlenaMykhailova-qq3sn 10 ай бұрын

Thank you a lot! Super useful!

@RezaDorrani 10 ай бұрын

Glad to hear that!

@user-ms8de5sy5u 7 ай бұрын

Amazing! Thank you so much for sharing!

@RezaDorrani 7 ай бұрын

Most welcome!

@dmitriyivanov1309 10 ай бұрын

Thank you! Excellent video that completely solve my problem

@RezaDorrani 10 ай бұрын

Glad to hear that!

@sankalpparmar4162 8 ай бұрын

Very helpful content explained in a very simple and easy manner. Thank you

@RezaDorrani 8 ай бұрын

You are most welcome!

@luisjimenez8760 Жыл бұрын

Thanks Reza. Great work as usual

@RezaDorrani Жыл бұрын

Thanks Luis

@vladimirbudija 5 ай бұрын

Good video. It helped me a lot to understand some aspects of Power Automate! Thank you very much, Reza

@RezaDorrani 5 ай бұрын

Glad it helped!

@MatheusFerreira-jp5ex 10 ай бұрын

Very nice, so helpful!! Thanks!!!

@RezaDorrani 10 ай бұрын

You're welcome!

@maryampour8914 Жыл бұрын

Once again a super useful video 👍thanks a lot😊

@RezaDorrani Жыл бұрын

Glad to hear that! Thanks so much for watching.

@officesuperhero9611 10 ай бұрын

Excellent! Thank you!

@RezaDorrani 10 ай бұрын

Most welcome

@oluwatobipaul6171 Жыл бұрын

Really useful video. I can think of few ways to apply it. Thanks Reza

@RezaDorrani Жыл бұрын

Most welcome

@VineetKumar-eq9lu 3 ай бұрын

Amazing helpful video!!

@macosusermac Жыл бұрын

Very nice! Thanks, mate!

@RezaDorrani Жыл бұрын

Most welcome! Thanks so much for watching

@kingparc Жыл бұрын

Great video. Thank you Reza

@RezaDorrani Жыл бұрын

Most welcome

@mauroalejandroescalantelei3103 8 ай бұрын

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.

@RezaDorrani 8 ай бұрын

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.

@yan-ct7it 7 ай бұрын

@@RezaDorrani Is there any way to bypass this limit?

@RezaDorrani 7 ай бұрын

@@yan-ct7it No

@vladsamoilov897 Жыл бұрын

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?

@RezaDorrani Жыл бұрын

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

@moshmoshpitpit 17 күн бұрын

Holy crap that is clever!

@RezaDorrani 17 күн бұрын

👍

@ranjanroy1623 Жыл бұрын

Amazing , thanks, and just to add and ask we need to have separate licenses too power bi and flows ?

@RezaDorrani Жыл бұрын

Yes, licenses are separate

@djnature1741 Жыл бұрын

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

@RezaDorrani Жыл бұрын

You can add a label to show its evaluation or simply select the formula in app edit mode.

@JenMayB 4 ай бұрын

Thanks!

@RezaDorrani 4 ай бұрын

Thank You!

@pradeepag161 15 күн бұрын

very helpful

@RezaDorrani 15 күн бұрын

Glad to hear that

@user-pc8vu4wn6m Жыл бұрын

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?

@RezaDorrani Жыл бұрын

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.

@davidlinx5 3 ай бұрын

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?

@UniqueTrickmaster Жыл бұрын

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?

@RezaDorrani Жыл бұрын

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

@datadragyn 10 ай бұрын

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

@ferranperezribera8910 11 ай бұрын

Very helpful! what if i wanted to do it periodically every morning isntead of when a power bi button is clicked?

@RezaDorrani 11 ай бұрын

The second part of the video is one that can be used in a scheduled flow.

@anjaramskov-qb3kh Жыл бұрын

Great video! Is it possible to run a recurring dax query where the flow overwrites an excel file? If yes, which sequence?

@RezaDorrani Жыл бұрын

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.

@shwetasharma4844 10 ай бұрын

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.

@RezaDorrani 10 ай бұрын

It should but not something I have tried

@MrJustdoit82 Жыл бұрын

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.

@RezaDorrani Жыл бұрын

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

@cavalcanteRJ01 3 ай бұрын

Great

@RezaDorrani 3 ай бұрын

Thanks

@tpedelaborde 3 ай бұрын

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 :)

@RezaDorrani 3 ай бұрын

I dont think we can increase that limit.

@thelovebeard3746 10 ай бұрын

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.

@RezaDorrani 10 ай бұрын

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

@user-jf8uu1ds1s 10 ай бұрын

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

@RezaDorrani 10 ай бұрын

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.

@mahtabsoin7239 Жыл бұрын

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.

@RezaDorrani Жыл бұрын

powerautomate.microsoft.com/en-us/connectors/details/shared_filesystem/file-system/

@MrTC-rv3jo 7 ай бұрын

Great content! will try to apply in the next few days as I have a similar task. Thank you!

@RezaDorrani 7 ай бұрын

Best of luck!

@harveypatricio 5 ай бұрын

Thanks!! if I do refresh query in the excel table it will update with latest in PBI dashboard?

@RezaDorrani 5 ай бұрын

No - you will need to refresh power bi dataset

@divyanair6147 Ай бұрын

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?

@RezaDorrani Ай бұрын

Thanks! Not sure about that.

@ShinChan.unofficial Жыл бұрын

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?

@RezaDorrani Жыл бұрын

I’m not sure if that is possible.

@ShinChan.unofficial Жыл бұрын

@@RezaDorrani Thanks for replying, i was wondering of it had the option like we used to get in sas eg

@RezaDorrani Жыл бұрын

@@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

@TK-pc3rw 4 ай бұрын

Hello, thanks for the video and explanation. Does it also work with large amounts and more than 500k rows?

@RezaDorrani 4 ай бұрын

1st technique: 2k rows 2nd technique: has a file size limit. Check documentation. Depends upon combination of rows and columns.

@TK-pc3rw 4 ай бұрын

what is the limit of the size?@@RezaDorrani

@RezaDorrani 4 ай бұрын

@@TK-pc3rw I don’t remember. You will need to check in the documentation.

@user-mt3et2te3v Ай бұрын

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?

@RezaDorrani Ай бұрын

I don’t know of a way to include DAX measures so not sure. Column names can be renamed using flow expressions

@fullmetalsweater1 10 ай бұрын

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 10 ай бұрын

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.

@fullmetalsweater1 9 ай бұрын

@@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 9 ай бұрын

@@fullmetalsweater1 I do not have any video references on it. Would have to try it out to know the steps.

@fullmetalsweater1 9 ай бұрын

@@RezaDorrani in case anyone comes through the comments looking for solution - the SELECT function resolved the issue for me as in this video kzfaq.info/get/bejne/or2lZ9FetKrJZIk.html

@gauravbrham7869 Жыл бұрын

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

@RezaDorrani Жыл бұрын

If depends on the size. Not on the rows. Too many columns would reduce row size.

@gauravbrham7869 Жыл бұрын

@@RezaDorrani Thanks a lot for responding

@krzysztofgorski8075 5 ай бұрын

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!

@RezaDorrani 5 ай бұрын

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

@manasabssreekantaswamy8401 Жыл бұрын

Hi Reza Is it possible to take the transformed table itself instead of selecting fields because additional column will be added later.

@RezaDorrani Жыл бұрын

You have to pass fields to flow.

@Erix-ni7ns Жыл бұрын

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...

@RezaDorrani Жыл бұрын

I believe you would need access to export from flow.

@radhikan2735 6 ай бұрын

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 ?

@RezaDorrani 6 ай бұрын

When flow is triggered from power bi, you get that users email as dynamic content.

@radhikan2735 6 ай бұрын

@@RezaDorrani Okay, Thanks Reza

@nehagoenka3900 5 ай бұрын

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?

@RezaDorrani 5 ай бұрын

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.

@user-db8uk5tk9h Жыл бұрын

Thanks for the video, but when I schedule my report for refresh, the .csv file is not getting updated, please advice

@RezaDorrani Жыл бұрын

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

@decentmendreams Жыл бұрын

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 ?

@RezaDorrani Жыл бұрын

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.

@Jana-sc7oh Жыл бұрын

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 Жыл бұрын

@@Jana-sc7oh I am not aware of a fix for this

@Jana-sc7oh Жыл бұрын

@@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.

@RezaDorrani Жыл бұрын

@@Jana-sc7oh Ok

@Salstravels Жыл бұрын

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 Жыл бұрын

I don’t know if that is possible. I’ll recommend checking on forums in case someone has done something similar powerusers.microsoft.com

@Salstravels Жыл бұрын

@@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.

@shaibeezz Жыл бұрын

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 Жыл бұрын

Worked - thank you

@RezaDorrani Жыл бұрын

Covered in video :)

@babuvt 4 ай бұрын

@@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?

@SyeraSyahira 14 күн бұрын

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.

@RezaDorrani 14 күн бұрын

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

@tolulopeesho852 Жыл бұрын

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?

@RezaDorrani Жыл бұрын

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

@tolulopeesho852 Жыл бұрын

@@RezaDorrani Thank you.

@xinranji775 10 ай бұрын

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.

@RezaDorrani 10 ай бұрын

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

@ManishSingh-ds3qn 10 ай бұрын

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.

@RezaDorrani 10 ай бұрын

No idea about that one. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com

@ramyagopinath8320 Жыл бұрын

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

@RezaDorrani Жыл бұрын

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

@hrithik-sharma 10 ай бұрын

same Problem

@RezaDorrani 10 ай бұрын

@@hrithik-sharma Forums will be your best bet

@AnandR-tp9oj 2 ай бұрын

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?

@RezaDorrani 2 ай бұрын

Welcome! I have done videos on sending emails with attachments. I would recommend checking my Power Automate playlist.

@AnandR-tp9oj 2 ай бұрын

@@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

@RezaDorrani 2 ай бұрын

@@AnandR-tp9oj I would recommend checking or posting your query on forums powerusers.microsoft.com

@AnandR-tp9oj 2 ай бұрын

@@RezaDorrani thank you

@anandraj6381 2 ай бұрын

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"

@charlesdawnbeltran9603 Жыл бұрын

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?

@RezaDorrani Жыл бұрын

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?

@charlesdawnbeltran9603 Жыл бұрын

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.

@RezaDorrani Жыл бұрын

@@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.

@charlesdawnbeltran9603 Жыл бұрын

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?

@RezaDorrani Жыл бұрын

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/

@kurthenderson6848 10 ай бұрын

Hi Reza, is it possible to do a query in Power Apps or Power Automate against a Dataflow directly? The only thing I've found is to publish a Dataset and do the query against that.

@RezaDorrani 10 ай бұрын

I am not aware of any option for that. Check on forums in case someone has a workaround powerusers.microsoft.com

@malli352 4 ай бұрын

@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..

@RezaDorrani 4 ай бұрын

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

@DAXifiedSatish Жыл бұрын

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

@RezaDorrani Жыл бұрын

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

@DAXifiedSatish Жыл бұрын

@@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

@RezaDorrani Жыл бұрын

@@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.

@DAXifiedSatish Жыл бұрын

@@RezaDorrani thanks Reza for your reply

@hrithik-sharma 10 ай бұрын

@@DAXifiedSatish dude if you got the solution can you guide me some how

@xaviushernandez3891 Ай бұрын

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.

@RezaDorrani Ай бұрын

Limit is on the size of the file exported not specifically on number of rows. You probably have a lot more columns.

@wodnyaquarius849 Жыл бұрын

Hi Reza. Gr8!vdo. I tried the flow and it works but no data gets exported into excel. What can i be missing?

@RezaDorrani Жыл бұрын

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

@wodnyaquarius849 Жыл бұрын

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…

@RezaDorrani Жыл бұрын

@@wodnyaquarius849 I have no idea.

@user-mt3et2te3v 2 ай бұрын

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?

@RezaDorrani 2 ай бұрын

You have to give users run only permissions on the flow

@user-mt3et2te3v 2 ай бұрын

@@RezaDorrani Thanks!! Works perfectly now!

@user-wp7re7dn4e Жыл бұрын

Can we use the output of "run a query against a dataset" into adaptive cards? I am trying since few days but no luck, any help is much appreciated.

@RezaDorrani Жыл бұрын

Possible. I do not have video reference on this and would have to try it out to know the steps. I recommend posting your query on forums in case someone has done something similar powerusers.microsoft.com

@user-wp7re7dn4e Жыл бұрын

@@RezaDorrani thanks for your response Reza. I have got the solution :) If someone has similar query, I can help

@user-lo8xh3wb4y Ай бұрын

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?

@RezaDorrani Ай бұрын

I am not aware of an option to do that

@shaibeezz 6 ай бұрын

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.

@RezaDorrani 6 ай бұрын

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

@shaibeezz 6 ай бұрын

@@RezaDorrani@RezaDorrani, thank you very much. I will wait for your next video.

@shaibeezz 6 ай бұрын

@@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

@HamzaAli-nh2le 9 ай бұрын

Hi, How can we create excel file with large dataset? As calling the data as per template will take a lot time... any suggestions?

@RezaDorrani 9 ай бұрын

This is the best approach I am aware of. You can check or post your query on forums in case someone has done something similar to your scenario powerusers.microsoft.com

@HarpreetGujral27 17 күн бұрын

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…

@RezaDorrani 17 күн бұрын

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 16 күн бұрын

Yeah, I checked there as well but all similar queries are unanswered. It would be great if you can prepare similar videos on this!

@RezaDorrani 16 күн бұрын

@@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.

@HarpreetGujral27 16 күн бұрын

@@RezaDorrani sure Thank You !

@saadsaffar8547 Жыл бұрын

Hi I'm currently using some measures in my table so the query for that table is returning min and max of those measures.... can a table with measure be used here? Please help..

@RezaDorrani Жыл бұрын

I am not sure if that can be used here.

@rayanmohammad910 Жыл бұрын

Thank you a lot very useful . Dose this work if I need to Export power bi data to pdf?

@RezaDorrani Жыл бұрын

Once you have the data in flow, you can use to perform actions of your choice.

@rayanmohammad910 Жыл бұрын

Thank you for your quick response

@rayanmohammad910 Жыл бұрын

Could I ask you a question which is not related to this topic plz?

@RezaDorrani Жыл бұрын

@@rayanmohammad910 I can guide or help depending upon wether I have done something related to your question

@rayanmohammad910 Жыл бұрын

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

@chinnapureddysivanagireddy3824 4 ай бұрын

Hi sir, Can we export multiple table visuals into same excel sheet?

@RezaDorrani 4 ай бұрын

Possible but not something I have tried

@AlexRuu Жыл бұрын

Hi Reza, Thank you very much...however weirdly it has worked from me partly. It is only limiting 7000 rows instead of 1000 rows. I have looked over and over my DAX but don't see the reason that I am still getting only 7K instead of 66K as I should. Any assistance will be very helpful

@RezaDorrani Жыл бұрын

I have not come across this issue hence not sure. I recommend posting your issue on forums at powerusers.microsoft.com in case someone has experienced something similar

@AlexRuu Жыл бұрын

@@RezaDorrani After further testing, I have found that the number of columns that required for the export influenced the number of rows...every time I have removed more columns the number of rows increased. Very weird this limitation

@RezaDorrani Жыл бұрын

@@AlexRuuIts probably a size limit. More columns means more size.

@gauravbrham7869 Жыл бұрын

Hi Alex, I got the same problem, getting all records with limited fields. Did you find something, let me update you in case I found something

@AlexRuu Жыл бұрын

@GAURAV BRHAM I might find a solution with doing the export from Power BI Paginated report...as it does not have any rows limits. However the solution a bit conveluted with setting parameters.

@AmkorTechnology 10 ай бұрын

How do you do it so it run an a schedule? How do you tell PA which Power BI graph/table to reference?

@RezaDorrani 10 ай бұрын

2 variations are covered in video. Flow triggered from power apps and flows triggered manually (which can be run on a schedule).

@AmkorTechnology 10 ай бұрын

@@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.

@RezaDorrani 10 ай бұрын

@@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.

@ubaidillahmuhammad20 9 ай бұрын

nice job. please put excel file in the description video

@RezaDorrani 9 ай бұрын

Thats the output file from my data. How would that excel file help you?

@truptinalnikar 10 ай бұрын

I am facing an issue where when I try to use the power automate expressions for renaming the file it opens the new expressions tab which is very confusing. Any way if we can stop the experimental feature in PowerBI - Power Automate?

@RezaDorrani 10 ай бұрын

It does not open flow in experimental mode to begin with. Not sure why that’s happening for you. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com

@philipedesouzasantos1201 4 ай бұрын

How will this function behave if you are using a free account and a public link? Will it redirect you to the SharePoint website to login?

@RezaDorrani 4 ай бұрын

Free account for? Public links for?

@jearsatisacro2774 11 ай бұрын

Hello do you have videos how to add some steps in formatting with date like this ''yyyy-MM-dd" and time like this "hh:mm:ss" when exporting from CSV?

@RezaDorrani 11 ай бұрын

I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.

@jearsatisacro2774 11 ай бұрын

@@RezaDorrani no worries. Thanks

@alisondeschenes3907 3 ай бұрын

Hi! When i try to export the csv file, all the date are grouped in one column, all separated with a coma, instead of havint multiple colums, how can i change that? thanks !

@RezaDorrani 3 ай бұрын

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

@bkollross 5 ай бұрын

I followed all the steps to export in Excel format, based on a table visual from my report, but the file created continued to be in .csv format - not adjusted to columns in Excel. Help! What could it be?

@RezaDorrani 5 ай бұрын

To create excel file is more work. Video shows csv output (check the demos) Export to excel- kzfaq.info/get/bejne/gdugrZpiyb29qY0.htmlsi=hRhesuhumkMZBAju You will need to mix and match the logic.

@renjithpl 11 ай бұрын

Hi, how can I distribute the csv file to user via email and it should filter only their content.. Kind of RLS.. Is this possible????

@RezaDorrani 11 ай бұрын

I do not have a video reference on this scenario and would have to try it out to provide guidance. I would say its possble though. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.

@rayanmohammad910 Жыл бұрын

Hello reza I tried to create pdf file from power bi data i created the file on onedrive inorder to convert to pdf How could the users for the report create the file if the flow saves the file on my ondrive?

@RezaDorrani Жыл бұрын

Send it out in an email to them

@mehranzafar8968 2 ай бұрын

Does it create a new csv file everytime we click the button, what if we want the same excel file to updated?

@RezaDorrani 2 ай бұрын

Possible but I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.

@4purs 11 ай бұрын

When I do this the export keeps limiting to 500 rows, can that be changed?

@RezaDorrani 11 ай бұрын

What is this? I show 2-3 options in video. Can you point to me to the time in video where this was shown?

@jreyesvega 10 ай бұрын

There is a path to have dinamic values for the filters?

@RezaDorrani 10 ай бұрын

I think I show that in end of video

@jreyesvega 10 ай бұрын

@@RezaDorrani yes but with manual trigger, when you copy the Dax the filters are fix. In my case dates, region, country etc. who I can pass this to the flow using the bottom that I create in the dashboard

@RezaDorrani 10 ай бұрын

@@jreyesvega I showed that in video. Please check end part.

@Pankajkumar-ot7su Жыл бұрын

Sir I want to learn complete share point . I have not read computer science before . So where have I start? And topic first cover and which playlist watch first on your channel?. Please reply

@RezaDorrani Жыл бұрын

My videos are based on Power Platform with SharePoint as a data source. I do not have any playlist specific to SharePoint learning step by step.

@Pankajkumar-ot7su Жыл бұрын

@@RezaDorrani thanks for repley. If you give me any suggestions as a trainer for SharePoint , power automate, power bi and power app learning that will be very useful to me . Because I have complete my graduation in civil engineering and I want to switch my career in it sector. Your teaching style is nice. Love from India.

@RezaDorrani Жыл бұрын

@@Pankajkumar-ot7su I dont have any trainer references for these. My videos are all organized in playlists. Beyond that, I do not have any references

@pranithashetty9841 7 ай бұрын

Can you kindly guide me on how to export data to excel from power bi and make that exported excel data get downloaded automatically to your local machine?

@RezaDorrani 7 ай бұрын

@arunv1909 5 ай бұрын

is there any way this could be changed to recurrence ?

@RezaDorrani 5 ай бұрын

The last technique shown can be made a recurrence trigger

@neakzyfr8174 10 ай бұрын

Hi, i'm using your tutorial to avoid the limit of 1000 rows with Run query against a dataset, but it seems not working anymore, do you have this problem too ?

@RezaDorrani 10 ай бұрын

I am not experiencing any issues

@neakzyfr8174 10 ай бұрын

@@RezaDorrani Did you try again the Run query into dataset ? You just change the 501 to 10000 right ? It's weird that's not worknig for me

@RezaDorrani 10 ай бұрын

Mine continues to work fine so not sure what the problem is with your scenario.

@neakzyfr8174 10 ай бұрын

@@RezaDorrani Imagine that I have a country filter on table data, i want to extract for many countries. If i'm using Run a query against a dataset, it will keep the same filter each time i run the flow right ? Should I create a variable to avoid keeping the same filter for each extract ?

@RezaDorrani 10 ай бұрын

@@neakzyfr8174 Not sure about this. I would recommend posting your query on forums at powerusers.microsoft.com

@user-ht9ct4ih8p 7 ай бұрын

I tried as you said but for me, options weren't coming except user ID, timestamp, email, and username so how could those items be coming into the list.

@RezaDorrani 7 ай бұрын

I have not come across this 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

@user-ht9ct4ih8p 7 ай бұрын

@@RezaDorrani I am using new version may be the reason

@RezaDorrani 7 ай бұрын

@@user-ht9ct4ih8p not sure

@SoumenGhoshal 3 ай бұрын

the only problem is that the column names are having square brackets. I wish there were ways to remove those brackets through the run query against a dataset. Please advise.

@RezaDorrani 3 ай бұрын

I don’t remember having square brackets based on demo shown in video. You can always rename the column names in power bi data set or use expressions in flow to remove brackets from names. I don’t have a video reference on how to do that in flow.

@SoumenGhoshal 3 ай бұрын

Skip to 15:15@@RezaDorrani. Can we rename the column Manufacturer[Manufacture] as just Manufacturer using Power Automate. The intention is to have a pre-configured data for power Bi as there are existing files with a certain structure. If we can crack this I guess we have complete one stop solution. As always you do a great job bringing these videos. Thanks in advance if you are looking into😊🙃 this. \m/

@RezaDorrani 3 ай бұрын

@@SoumenGhoshal Possible by using expressions but not something I have a video reference for. May be try posting your query on forums in case someone has done something similar powerusers.microsoft.com

@imanfatima3984 Жыл бұрын

can you make a vedio on export in word and directly send to configure email.

@RezaDorrani Жыл бұрын

I make videos when I receive multiple requests on a topic. I have ideas in my backlog with over 30 requests. This is the first request on this topic.

@dmitriyivanov1309 10 ай бұрын

How should I transform a cicle step if don't lunch by button, and use a step Run Query Against Dataset?

@RezaDorrani 10 ай бұрын

I did not understand the question.

@dmitriyivanov1309 10 ай бұрын

@@RezaDorrani In the beginning you added required fields in power automate visual fields, but there is another way to get data - it's use step "Run query against dataset" inside Power Automate. The advantage of this way that I can build a proper table with a DAX, but I did't find a way how to iterate the result of a step "Run query against dataset" to add a row to Excel file.

@RezaDorrani 10 ай бұрын

@@dmitriyivanov1309 Sorry but still did not understand. Video is a step by step guide. If you ran into issues then I would recommend posting your issue on forums at powerusers.microsoft.com

@MrSunnychadha 5 ай бұрын

my power bi dashboard has records of 60000. Run a query against a dataset i have changed the top row limit to 60000. When running the power automate only 17000 records are getting inserted into the sharepoint csv file. Any suggestions on why is it not picking all records of the dashboard.

@RezaDorrani 5 ай бұрын

There is a size limit for that api. It not only depends on rows but also how many columns.

@MrSunnychadha 4 ай бұрын

@@RezaDorrani is there a way I can get all the data ?

@RezaDorrani 4 ай бұрын

@@MrSunnychadha Im not aware of a way to do that