Export from Power BI to Excel up to 🔥100k rows 🔥| Run a query against a dataset
HTML-код
- Опубликовано: 14 июн 2022
- In a previous video ( • How to create a Export... ) I showed you how to export your data to excel using a button in Power Bi but there was a 1000 item limit, but I found a workaround and now you can export up to 100000 rows or 1000000 values per query!
To do that, we are going to use the new Power Automate operation called | Run a query against a dataset
FourMoo Blog: www.fourmoo.com/2022/06/15/ex...
Announcement: powerbi.microsoft.com/en-us/b...
More info: powerbi.microsoft.com/en-us/b...
If you want to learn more about exporting Power Bi data using Power Automate, check the entire playlist:
• Export data from Power BI
Have fun!!
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
************
MY GEAR: You often ask me what software I use to edit my videos, so I thought I let you know in the description and give you a discount code so you can get it too!
COUPON: CURBAL10
Note, if you purchase the software using the link or code, I will receive a small commission and you will indirectly support my channel. Thanks in advance!
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
That's a treat for us!!! Great and worthful share Ruth.
And thanks to Gilbert for the inspo!
This was extremely helpful for expanding how to perform this function with all the various options, thanks so much!
So much needed. Great stuff and features from Power tools. Appreciate your help.
Ooooh! Qué maravilla!
Si no existieras habría que inventarte. Muchas gracias Ruth!!!
😂😂 Muchas gracias Nadia!
Thanks Ruth. Very useful.
FANTASTIC! it's exactly what I needed! Thank you!
🥳🥳
This is the first method I actually got to work in Power Automate thx!!
Bravo!!
Thank you it was simple and quick solution.
Really useful video. I have been asked to do the same against of a number of on prem SQL Server tables. My first thought was to create a new Dataset with them ( using the new Power BI Report and Dataset support in Solutions) or perhaps just point a simpler flow to the tables themself via the gateway. I wondered what the pros and cons are with both approaches.
Hi Ruth. @3:40 Did you know, after starting the Performance analyser, you can click on the visual specific icon (to the left of the filter funnel icon) that looks like a table with a magnifying glass. That refreshes only the measures required for that visual.
Yes, i remembered that today !! 🤦♀️🤦♀️
Amazing! Thank you so much!
Awesome Ruth :)
Eso es muy inteligente - muchas gracias!👌
Thanks so much for the video, this is unbelievably useful
Love it too !!
Thank you fir such a wonderful video
Hi Ruth...thanks for uploading this video, it was very helpful, i was struggling with this for a week. I would like to know if you can help with another video to convert this CSV to excel table format...
Thank you very much!
Hi Ruth, Thank you so much for the video. U have been so helpful and inspirational to my transition to an analyst role. Thank you.
Regarding this video, I am not able to query the data set as it's a model hosted in SQL Server Analysis Services using on premise gateway.
Is there any way around it?
I am searching for this. Thanks a lot
You welcome:)
WoW @Ruth...Needless to say but I will still say.. this is amazing.. 😃😃👏👏
This will be super useful tip for all PBI developers.
And I have discovered even more use cases ! Coming soon!
Very Useful, can’t wait to try it out. Respect for your time management skills to produce this content and write useful comments in response to questions. Is the secret ingredient Coffee?
I love creating data, videos and even more chatting with all of you! Wouldn’t have it any other way ☺️
Thank you so much!!!!! My hero!!
🎉🎉🎉
You look great like usual
much appreciated!
Thank you... Excellent content
Thank you !
Thanks, it works good 😎👍
Thank you!
Pleasure !
This is amazing thank you! I wish the limit was 1mil!
Maybe soon 😁
Excellent video
Great to hear :)
Thank you!!!!!
Hi Ruth, greate tutorials :-) Question, do you have any videos of creating measures that ignores any other filters on a page? Example being a measure calculating current weeks sales and ignoring any other filters/ date slicers etc. on the page.
Apply ALL() dax func on table with the measure you used to calculate current week sales or else you can use all func inside calculate function.
Nossa me salvou aqui!!
Great!
HI, Thanks a lot for the help. Im having the following issue: The 'from' property value in the 'table' action inputs is of type 'String'. The value must be of type 'Array'. On the create CSV table. Thanks in advance.
Thanks Mam !! wonderful !! Subscribed and shared !!
one Quick question , for making this work do we need to have our dataset configured in Power bi service ?
Yes :)
Hi Ruth. How would you filter by date - say you only want records equals 2021?
Hello, thank you very much for all your videos. I have a last but big problem with my CSV export : the delimeter with "," instead of ";"... do you know a way to change that please ?
Hi Ruth, thank you for this video tutorial, It´s very helpful. I would like to know if you can help me with the name of columns, after all step, the csv receive the name of query before each field. Example: 'Employees' [ID], 'Employees' [Name], 'Employees' [Age], but I need only the name of field [ID],[Name],[Age] in the CSV
Hi Ruth! Do you know of a way to export from power bi to a sharepoint list?
Thanks Ruth, i can pull the data from PBI to SP, but the challenge is all headers are appearing like this "[Control Procedure]" with the [ ] how to get it removed?
super!
🎉
@Ruth. I am new to Power bi and learning from your videos. My query is visuals are made from data uploaded, what way this export from Power BI visuals help to get the same data we already have.? Kindly guide, thank you.
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.
Good video, I have a question I hope someone can answer it, someone knows if you can export directly on the computer or can only be exported through sharepoint
Genial!
🎉
Hi Ruth,
We are exporting large data from power bi to excel, with above implementation and it is working fine.
But when we are changing the slicers data and then export the data we are not getting the filtered expected data.
How we can implement the logic where options selected in filter should be exported on excel /csv
Hi Ruth, is it possible to export data from pbi visual to multiple sheets in a csv?? America data to sheet 1, Canada data to sheet 2 of a csv?? Thank you!!
This CSV export. do you have video for excel export?
Hi Ruth, thank you for the video! Do you know of a way to change the column names within the output? So it meets the column names as ingested?
You can use a CSV to Excel template for something like that: powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/td-p/1826096
Hey! Ruth has another video on this topic: ruclips.net/video/lWu4n-ODe4U/видео.html
Thanks RUth
@@tylerkolotaExactly this is what I was looking for.. Thank you
Not sure why Microsoft didn't provide a direct Excel Export task in Power Automate
Hi Ruth. Thanks for the video. Do you know how to use these button when we upload de report to Web (public) (?) (sorry my english is not good :( )
Great video! I am having trouble finding the next video where you talk about emailing the filtered data to the sales team... What is that video called? Thanks again!
I think it is the one about smart alerts:
m.ruclips.net/p/PLDz00l_jz6zze26MVT-0YV7qcjismMFFo
What if you want to export a table that is affected by user selected slicers? Would you be able to export the filtered table?
Hi, great video!
QQ, can you save it as a .xlsx rather than CSV?
Check the playlist i mention in the description box, I have a video on that
Hi Ruth!
I did as you mentioned. But still I am getting 1k rows only.
Hello Ruth, Awesome video.
I have one question, How can I filter the data from the query using the date selected on the slice?
Check the playlist in the description box, it is all in there :)
Great stuff 🙏 Can you please make a video on how to export power bi data to sql table instead of excel ?? Thanks in advance
I have one , search on my RUclips library
Is it possible to do this based on row level security and using a html table in the body of an email without generating a CSV file?
Hi Ruth, thanks for the great video. Is it possible to email this data instead of saving as a file? My users use the PBI iPad app. They can’t export a visual using the regular process of 3 dots and export. But if I can place a button that upon click could email them the data, that will be a great workaround. Is it possible?
Yes, video on that coming this week :)
Do we have to pay additional price if we use power automate? Secondly, will it work with power bi embedded also because I want to integrate my dashboard with my web. Thanks
How did you get edit option in the ...?
one Quick question , for making this work do we need to have our dataset configured in Power bi service ?
hola Ruth, solo tengo 2 dudas:
1) por qué la vista previa de tu archivo CSV se ve ordenada? en mi caso se ve separada por comas :( algun consejo?
2) como cambio el nombre de las columnas en la consulta DAX? para que no salga el nombre de las consultas como prefijo?
Muchas gracias, me funcionó genial!!
1. Es un csv, si quieres columnas guárdalo como un archivo de Excel
2. Cambia el nombre en el modelo
Genial!
Hi Ruth, I am doing similar thing where I am exporting entire table which consists around 49K rows and 39 columns. But I am getting only 15K rows into csv file. Kindly guide me on this.
To do this, do we require any other license apart from Power BI Pro license? Reason I am asking this because it throws error - 'It looks like something went wrong' when I follow your steps.
is there a way to get the button to work for any user? Currently only works for the user who set up the flow
Very useful. Would it be possible to take the query result and update records in Dataverse instead of sending it to a csv file?
Dont know, maybe someone in the power automate community knows?
Excellent video!! I was able to export all the data but the date column has a long format even though it was short format in the dashboard. Do you know why? How to fix it?
formatDateTime(parseDateTime(item()?['YourTable[Date]']), 'M/d/yyyy', 'en-US')
Unfortunately, does not work. Want to use a button to data dump to CSV - dataset has 89,000 records. The result produces around 25,000 records. Found an article that says the maximum number of rows that Power BI can export to a .csv file is 30,000 and to an .xlsx file 150,000. It seems I'm getting a sampling result. Articles points to using Dax Studio to lift that limit. Thoughts?
Hi ruth, how can we allow our end user to download the data into excel. When I am trying to use the above method I can download in my local machine but my end user can’t download it
on clicking the edit option in Power Automate it shows blank, don't know why?
so does anyone know the workaround for if you have international characters, ie, chinese characters?
What if I only have read only access to the dataset, is it still possible?
Is there a way to export report data to an excel file and keep all formatting on a table? I have a report table with conditional formatting (cell colors and icons) and I need my end users to be able to filter the report however they need to for each store. Then they would export each desired filtered table, with the formatting, to give to their store managers as an excel file. The store mang. would then be able to create there weekly/monthly plans.
This might work for you:
ruclips.net/video/iS8AAV4CS8I/видео.html&ab_channel=Curbal
Hi Ruth, the big downside of this vs getting data from button visual is you can't get your CSV file to be dynamic based on the filter context 🙁.
it looks like you can, check one of the docs i posted in the video description
Create a paginated report to export data if you want dynamic prompts. These reports can be formatted unlike the Power Automate export is simply a plain dump. Paginated reports also give you flexibility of scheduling and passing parameters through Power Automate schedule. If you want a CSV and don’t have a O365 license you can’t use Power Automate so in this case Paginated Reports come in handy. Here you can also export more than 100K records.
@@NareshOjha isn't paginated reports Premium feature?
Yes. You need a Power BI Pro license to publish a report to the service. You can publish and share paginated reports in your My Workspace or in workspaces, as long as the workspace is in a Power BI Premium capacity
I am having 2 issues. 1) My query has 23 columns and I need them to stay in the order but they are getting all jumbled up. 2) I have data with leading 0s and the query is changing them to numbers and loosing the zeros. Any advise?
Hello Curbal, is there any restriction to triggered the button when the dashboard is published ? Because everything works perfect in desktop but not when I executed the button in published report
Maybe a bug? Report it at issues.powerbi.com
Thanks for sharing it.
Can it be xlsx extension instead of csv? Also, can it download in Table format so that no need to apply additional steps of "create table".
Yes, check my previous video for that
@@CurbalEN could you please share link of it? Not able to find it.
It is in the video description, first link!
@@CurbalEN Oh Ok, That way would be slow and need to have predefined excel. To avoid that, I use currently Paginated report. I hoped DAX query based solution can replace creating extra paginated report and create table steps in Power Automate.
Hi @crucible, I am only getting 1000 rows data while extracting data from power bi. Please help its highly urgent.
Hi Ruth- wonderful video. But instead of exporting to sharepoint or online excel. How can we download to our local machine, like we do after clicking 3 dots and export option.
Check my latest videos , I show how there .
@@CurbalEN Thank you 😊
Tried this again, filters don't appear to work with this method.
Thanks for the video. Today we got the same requirement :)
Do we need Power Automate subscription for this?
You need an o365 license that includes power automate
Create a paginated report to export data if you want dynamic prompts. These reports can be formatted unlike the Power Automate export is simply a plain dump. Paginated reports also give you flexibility of scheduling and passing parameters through Power Automate schedule. If you want a CSV and don’t have a O365 license you can’t use Power Automate so in this case Paginated Reports come in handy. Here you can also export more than 100K records.
It is not a simple dump, more videos coming soon 😄
Yes Ofcourse you can format the excel export using Office Scripts / CSS but that is more of a work + If we need to make it prompt driven the paginated report (or the paginated visual within Power BI Desktop) will be easier. This is what I think but Ofcourse I am waiting and excited for further videos from you 😊
I am not sure it is what you mean, but I will show you what I have next week :)
Thank you so much for that video.
Can I exceed power bi 150k row limitation (excel) using this method or any other method? As well as I want to export filtered data to excel file. If any method for that plz mention.
Maybe with datamarts or dataflows? It depends on what you are trying to do…
@@CurbalEN I create table using power bi table viz. It contain near 250,000 records considering the 1year period. I want to export this visualization record to excel file with applied filters. Can have any method to do that?
I used several table columns and measures to create above mention viz.
Not from power bi , or I don’t think so
Have you tried to run the query for the TOP 100k, then skip 100k and get the 100001th onwards?
@@gulhermepereira249 Thank you for your comment. I want to do it one time process.
Ruth's solution is great, but now the question is, how do we pass the filters that we apply to our report dynamically? :'(
Greetings from Ecuador
It is explained on the playlist or here:
curbal.com/curbal-learning-portal?kb-search=export
@@CurbalEN Gracias Ruth :)
Great vid - do you know if it works with RLS?
EDIT: nvm - spotted it in the documentation!!
Cool right? :)
@@CurbalEN it is, will be interesting to see if theres a difference between an internal user vs an external/guest user
This is good for dumping all rows but just in case if I have sliced the report and rows are more than 1K then how to do it?
I have another video on the playlist for that user case
Hi, can I set this up so every automatic update my report does, it appends new data to an existing excel file?
Most likely? Haven’t tried it though
Can we email the exported csv file onedrive link through power automate?
My guess is yes?
yes, you can
Hi,
CSV output from run a query against a dataset is limiting rows upto 5237 rows. Can you please help?
Check my no row limit video
How can I trigger the flow without pressing the button? I need it to run once a week automatically
Don’t use the button functionality, schedule it from power automate
@@CurbalEN Do you have a video showing how to do it? From Power automate I can not access the information of the metrics that I have in power bi
Can it be exported to local folder rather than onedrive?
Yes, will do a vid soon
Hi @curbal I am getting only 1000 rows data when extracting from power bi. PLEASE HELP ITS HIGHLY URGENT.
Surely you have not removed the section where from the TOPN
does it work with Google Sheets? I can't get it to work for google sheets
Dont know, try asking in the power bi community
Hi, any idea why I am getting incomplete CSV? In PBI there are ~70k rows, hence the export is only 2,893 rows. Is there some other limitation to this approach other than 100k rows? Thanks
Yes, quite a few more, check the docs!
@@CurbalEN thanks, it makes this not useful for my case :(
Check what they are, you might be able to get around them
for me its not working :( . i have 20000 rows and 21 columns its exporting only 14k using power automate :( All i want is a daily schedule export (An entire powerBI table without summarizing )
I did it step by step and it only downloads 14466 rows out of 56000
My biggest doubt is can we load this directly into sharepoint list instead of exporting into Excel , please can anyone support
Hi Ruth, when i try it doesnt give me all of the rows!
It has a limit of 100k
@@CurbalEN i understand but i have data of 30k while using this its not giving me even 15k, however when i use the same by creating a table in power bi it gives me whole 30k. pls help or suggest!
@@astralyog yeah, same for me, did you figure it out?
IT'S WEIRD, BECAUSE I did all the steps and only get 80 000 rows of 150 000
TIPS: Folder Path: /Delade dokument/General
100k doesn't feel like enough.
Look it at this way, we could only do 1k before ;)