Export MORE THAN 100k rows of Power BI data to CSV using Power Automate

Export MORE THAN 100k rows of Power BI data to CSV using Power Automate | No ROW limits!

Рет қаралды 51,500

Күн бұрын

In this video I will show you how to batch the Power BI exports so you don't hit the Power Automate limit of 100k rows.
Get the code shown in the video here:
curbal.com/curbal-learning-po...
Chapters:
00:00 Intro
01:40 Code1: Flow to export Power BI data using Power Automate (will hit the limit)
03:15 Explain the logic first
05:50 Modify the flow to batch the exports
06:00 Initialize the row and increment variables
08:15 Code2: Count number of rows in Power BI
09:40 Code3: Convert the array to number
12:00 Start the loop
13:00 Code4: Rewrite the DAX code to filter the tables
14:30 Increment the variables
18:00 Append all exports to one file
All Export videos here:
• Export data from Power BI
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

Пікірлер: 277

@zmot 8 ай бұрын

For those wondering how to stop the headers from being printed every time the loop runs you can do this: 1. Initialize the AppendCSV variable using your headers in CSV format followed by a newline character (instead of it being initialized blank) 2. When creating the CSV use 'advanced' mode and set the values one by one to be item()?['ColumnHeader'] (leave the header blank)

@harrisonsoares5242 7 ай бұрын

Can you show me an input example for AppendCsv variable? I'm trying to do this, but it's not recognizing as row. I think I'm writing the notation incorrectly.

@steviesimsii Жыл бұрын

Great job Johan, Henk and Luc on your work!

@TheRatnakar2007 Жыл бұрын

This is super help. Thank you!!! Below is solution for Duplicate headers. Try the below formulae: Do Until loop -> Append to string variable if(equals(variables('LoopsVar'), 2), body('Create_CSV_Table'), join(skip(split(body('Create_CSV_Table'), decodeUriComponent('%0A')),1), decodeUriComponent('%0A')))

@karthiram8393 Жыл бұрын

Thanks Sir ....For sharing the solution...it is working for me

@sedwards2207 Жыл бұрын

Works! Thank you so much for sharing!

@irene751 Жыл бұрын

Thank you! :)

@wexcel-ur2sj Жыл бұрын

Hello, I tried adding this step, but I was unable to because the Initiliazed variable is an Integer. Can you explain how I go about doing this?

@lixie463 Жыл бұрын

I don’t understand, where do I copy and paste this code into to avoid duplicated headers?

@ramyagopinath8320 11 ай бұрын

Great content❤ worked like charm. Was struggling from past one week and with this video, was able to accomplish what i wanted😊

@RyanHare Жыл бұрын

This works really well! The only thing I've noticed it that it includes the headers in every loop it does. However, I'm sure a little step to remove that should be easy.

@sumanthd9319 8 ай бұрын

That happiness of yours at 22:32- 22:36.. I too just felt the same. Nice work!

@CurbalEN 8 ай бұрын

😂 Shared happiness is 2x better!

@bertoso Жыл бұрын

Muito obrigado por compartilhar seu trabalho, me ajudou muito em desenvolver o que preciso. Sucesso!

@CandiceClarke Жыл бұрын

This was amazingly simple to follow and as you did suggest, it requires precision. I had one error that took me days to find, until I just copied all of your code. Thank you! Subscribed.

@CurbalEN Жыл бұрын

Welcome!

@CandiceClarke Жыл бұрын

@@CurbalEN I see that you do enjoy when your subscribers ask you follow up questions. My end-users are in different countries and are only sanctioned to view their own work. I was able to apply a filter so that I can generate a CSV for each territory. Is there any way to generate multiple files (based on a Territory filter) from a single Flow?

@CandiceClarke Жыл бұрын

@@CurbalEN The appended CSV file also appended the column headers :(

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

This is super help. Thanks Curbal! I will never figure it out without your tutorial

@CurbalEN Жыл бұрын

Glad it was helpful!

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

@@CurbalEN Hello Curbal, I also got the same issue that the column names repeat multiple times. I couldn't find a good solution to that. Would you mind helping find solution if possible?

@CurbalEN Жыл бұрын

It is on my to-do list ;)

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

@@CurbalEN Good to know it! Thank you Curbal. You are awesome and made me see the hope.❤!

@PublicSite Жыл бұрын

Hi. This is a great video that you've posted. I learned quite a bit by following it step by step. It does exactly what I wanted to achieve. Works like a charm. Thank you very much for sharing your knowledge. This is super helpful. Kudos to you!!!

@CurbalEN Жыл бұрын

And to you for the feedback!

@nishthatank5954 10 ай бұрын

Hey does it works with dashboard filters

@CurbalEN 10 ай бұрын

@nishthatank5954 I dont think so :(

@nishthatank5954 10 ай бұрын

@@CurbalENhey can u please which will work for solution will work if we apply filters on dashboard and then export data

@CurbalEN 10 ай бұрын

@nishthatank5954 i dont think it is possible

@user-my9zj2pd6h 4 ай бұрын

This was super helpful - thank you!

@qc4040 Ай бұрын

Thank you! Exactly what I needed

@ryannakao2448 9 ай бұрын

Your content helped me so much, thank you for that!

@CurbalEN 9 ай бұрын

😃

@BassFever4Ever Жыл бұрын

This is a very useful lesson. Thank you!

@CurbalEN Жыл бұрын

Wonderful!

@LincolnOliver 10 ай бұрын

You are amazing!! It helped me a lot!!

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

This is fantastic! Thank you so much!!!

@basel777 Жыл бұрын

Just wanted to let you know that you're my Power BI hero!

@CurbalEN Жыл бұрын

💛

@sharavananp5570 Жыл бұрын

Super duper. And that's how it's done . Really useful

@CurbalEN Жыл бұрын

Yeah !!

@raymchin007 8 ай бұрын

Excellent video - quick question What do you do if you don't have or cannot create an index column? I've recreated the report from a PBI data set (the data does not belong to me) and I don't have a unique identifiable column as the data changes on a daily basis - let's say my data has multiple claims (sometimes duplicate claim numbers) and duplicate dates. Any insight is appreciated.

@aritramech 11 ай бұрын

Excellent video.. I am going to create my own flow like wise

@anistplenitudmampuru8596 3 ай бұрын

For nulls and Keeping column order : Use a SELECT action to map the column names to the data values - this takes care of the nulls and the column order ... I know - you have to map all the columns to the incoming powerBI values. This worked for me with over 100 columns. @Curbal Thank you for this Tutorial!!!!

@chirschmann3 2 ай бұрын

Can you expand on how you do this? I'm dealing with this exact issue right now!

@anistplenitudmampuru8596 2 ай бұрын

@@chirschmann3 The Select action in Power Automate can be used to map column names to data values. On the "From" input use the "firsTableRows" output from your "Run Power BI Query " action, then in the "Map" parameter which is displayed as a table, on your left hand side, type in the column names you want in your csv e.g FirstName , then on the right hand mapped to the FirstName will be the result from the query something like item()?[TableName['ColumnName']] ... you have to type this in as an expression. Then do the same for your other column name and the value you want them mapped to from the Power BI Run Query action. Power Automate doesn't automatically let you select an item from the firsTableRows output so just ensure you use the expression

@Knolram 2 ай бұрын

This is an excellent solution for smaller data sets and really helped me get on the right path to making this a workable solution, but I found that adding the select action exponentially increases the run time for the flow the bigger the dataset is. To get around this, instead of adding a select action, I added placeholders for any null values in the Power BI table I am pulling from, so that there are no null values being pulled by the flow from the table. To do this I created a simple DAX table that pulls all the values from the table I want to export via Power Automate. I then went through the DAX table calc and added code for each field being pulled into the DAX table to return the string "~EMPTY~" whenever a value is blank. Example for pulling the [Address] field from the main table into my DAX table: "ADDRESS", IF(ISBLANK([ADDRESS]), "~EMPTY~", [ADDRESS]), If the field type is not text, say a date or a number, you'll need to convert it to a text format like General Date so you don't end up with a type mismatch between the values that exist and the "~EMPTY~" text values you're getting for blanks: "DATE_ACTIVATE", IF(ISBLANK([DATE_ACTIVATE]), "~EMPTY~", FORMAT([DATE_ACTIVATE], "General Date")), After doing this for all the fields, there will be no null values in your DAX table, so you can pull from that table into your flow and the column order will always be the same for each loop. Then when you pull the data from your CSV files in the future (in my case I'm pulling them into Power BI), you can use the query editor to convert all "~EMPTY~" values back to blanks before assigned column types like Date, Whole Number, etc.

@ahmadbelal2412 Жыл бұрын

Well, just wanted to let you know that I exported more than 165K rows. Your method is the best. Thank you so much for sharing

@CurbalEN Жыл бұрын

Delighted to hear! 😊

@ahmadbelal2412 Жыл бұрын

@@CurbalEN I just have one observation. I noticed that some of the fields are missing , around 10% of the original data is missing. Is there something that I can do to overcome this obstacle?

@CurbalEN Жыл бұрын

You are probably hitting the api limit, reduce the download size until you get all your rows

@Knolram 2 ай бұрын

It would be difficult for me to articulate the size of the problem that you helped me solve with this video. Thank you SO MUCH!!!!

@marcusdipaula Жыл бұрын

Thank you!!! I needed this!

@CurbalEN Жыл бұрын

Perfect timing then!!

@bheemashenmudgal5771 Жыл бұрын

Great video.. This video is very helpful. Can you please add the same video with slicers/drop down filters for data? Or provide some tips as how to achieve the same with drop-down filters.

@AlexRuu Жыл бұрын

Yes, this can be very useful

@ThomasHampe Жыл бұрын

Thank you SO MUCH, I am learning so much from your videos. ( PS: It works ! )

@CurbalEN Жыл бұрын

Fabulous!!

@hichamfaidi Жыл бұрын

@@CurbalEN I'm trying to export data from my Power BI dataset to a CSV file on SharePoint. My dataset contains over 1 million rows, and I've tried adding an 'index_' column using RANKX based on an ID column. However, when I export the data to a CSV file, I only get 2000 rows in the output. I need help with this issue. Can you please provide some guidance on how to export the full dataset to a CSV file on SharePoint? Thank you.

@jayong2370 Жыл бұрын

Nice job you guys! So smart 🙂

@CurbalEN Жыл бұрын

Really cool :)

@user-pd1vv8zo8j Ай бұрын

Gracias por sus buenos aportes!

@rayromych5534 11 ай бұрын

Thanks for the great and useful video Is there any way to sort data by different columns so output csv file is sorted by specified column?

@analyticsadda6080 Жыл бұрын

Thanks for this video. Really useful

@CurbalEN Жыл бұрын

🥳🥳

@JegErAlan Жыл бұрын

Great video as usual Curbal, thank you! BTW, the reason I may use this is not because I ran into a row limit on running a query against a dataset. Instead, it appears to be an "amount of data" limit. If I put fewer columns in the query, I get more rows. I'm not saying there isn't a 100K limit on rows, but with a decent number of columns, I couldn't get more than a few thousand back. I zapped all but two columns, and I got all 70K. Short version: At least with the Power BI run a query step, I wonder if it more of an amount of data issue than a row issue. But your video is extremely helpful, regardless!

@CurbalEN Жыл бұрын

It is both, if you run into a limit , make your loops smaller :)

@JegErAlan Жыл бұрын

@@CurbalEN Exactly what I plan to do. :-) I’ll admit, I was surprised that the run a query step was so limited. Thanks again!

@CurbalEN Жыл бұрын

Ja, api limits always suck :(

@anbum4511 Жыл бұрын

Good job, thanks for the video. As per this flow the data appending with headers, could you please help me out how to remove the headers from appended data. Thanks

@miragliag Жыл бұрын

as always.... just amazing! Thanks!

@CurbalEN Жыл бұрын

Thank you!! ☺️

@hichamfaidi2857 Жыл бұрын

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

Was able to do 186K rows with 17 Columns - just had to increase the do until limit. The column repeat isn't ideal but also not a true issue as I am only using this to create a repository of historical data.

@michaelj45 Жыл бұрын

Hi Curbal, thank you for this video which helped me to automate my download process.. I would like to know if its possible to export more than 100k rows without Index column as I am using live dataset

@robsommerville1694 Жыл бұрын

Fab video and concisely done, as said though you need to be exact with the scripting but once up and running its exactly what's needed. Shame it wasn't for Excel rather than csv, can this same process be done for excel and is there much deviation from this flow? Very helpful and can'r praise enough those that take the time to create these videos and share their skills and knowledge. Massively appreciated. Happy days

@CurbalEN Жыл бұрын

🥳🥳

@hichamfaidi Жыл бұрын

@@CurbalEN hello, I'm trying to export data from my Power BI dataset to a CSV file on SharePoint. My dataset contains over 1 million rows, and I've tried adding an 'index_' column using RANKX based on an ID column. However, when I export the data to a CSV file, I only get 2000 rows in the output. I need help with this issue. Can you please provide some guidance on how to export the full dataset to a CSV file on SharePoint? Thank you.

@srilaxmigrao2734 Жыл бұрын

Hi good video. Just one query can we add these data into excel by overwriting the excel every time?

@rockydon2262 Жыл бұрын

Thanks Curbal for this great series! One question, did you try to get those DAX query automatically from a report? (as sometime the query is not predefined, they must be captured with dynamic content and filters)

@CurbalEN Жыл бұрын

I don’t think you can do that? Might be wrong though…

@rockydon2262 Жыл бұрын

@@CurbalEN I got some clue one that, but most are complex; so curious if someone'd done that before; (obviously M$ had done that in PBI Desktop, but not sure how)

@CurbalEN Жыл бұрын

I haven’t done it, but if you can point me to a blog where someone has successfully done it I can take a look :)

@charlini.b146 10 ай бұрын

This is exactly the scenario I have in the company I work for, I need to export millions of lines per day to csv, but I need to insert start date and end date parameters, how could I do it in this case, defining (filtering) the time period in the power BI matrix or table to then start the power automate task or do I define the time period in the same way described above and consider these in variables? I would really like your help! Thanks for sharing your knowledge with us!

@aydeediaz2307 11 ай бұрын

Hi! I see you are using your dataset as your report. In my case I have a dataset that feeds different reports, and I want to do this from a specific one, how can I do it that way? Because the dataset has a lot of tables inside it. Thanks! great video!

@riyajadon9794 Жыл бұрын

Hey, awesome tutorial, can you pls tell us how to remove extra header after getting the data

@mariasilverdale6444 Жыл бұрын

You are the best!

@adsuk2003 11 ай бұрын

Hi Ruth - I've found this extremely helpful. Can you please tell me is it possible to have a button that exports to the current Users (report viewer) My Documents folder OR their own OneDrive, as automate seems to only allow it to export to the report creator.

@ashwinjoshi3331 6 ай бұрын

Thanks a lot for the video. It was really helpful .I could replicate it at my end. Just one question - can we call the same Power Automate flow in Power BI i.e. on a click of a button ? I tried it but could proceed only with few steps . Later it was not showing expression control . Is there any other way by which we can call flow ?

@tolulopeesho8103 Жыл бұрын

Thanks so much for this video, Curbal. Please how do I create a filter table to batch the data without an index column?

@michaelj45 Жыл бұрын

HI were you able to find a solution to this?

@esther140493 3 ай бұрын

Hola! Gracias por los videos! Hay alguna forma de crear un botón en un dashboard que extraiga todos los valores que aplican a los visuales que se muestran? Muchas gracias!

@Cbabel-tr5sd 2 ай бұрын

This is extremely useful! helps me to resolve But am I the only one encountering the below notification & workflow stuck at running when exporting more than 600K rows "Your flow is consuming an excessive amount of data and reached 80% of the content throughput limit. If the usage grows, further actions may be throttled or slowed down."

@mineeeeful Жыл бұрын

Hii! Isit possible to have a dynamic folder path when creating file in sharepoint/OneDrive? Let’s say I’m gonna create a new folder every month (naming it the latest month). Can I make my flow with a dynamic folder path to choose the folder to save in based on the existing month?

@eddie_9190 11 ай бұрын

Hi @CurbalEN I got the flow working but I can't seem to replicate it for the Excel version download (XLSX)? The export automatically turns my dates to DateTime format, and it doesn't allow re-formatting in the CSV when I try to change it to a Short Date. Is there a way to replicate this exact flow but as an Excel file? Am I over complicating it? Thank you again for everything Curbal!

@mubasshir_ansari2134 Жыл бұрын

Hi, Your Videos are so Useful, Please could you let us know which Power automate License you are using to export 100k rows in Excel.

@CurbalEN Жыл бұрын

I have office 365 E3

@robyip1843 Жыл бұрын

Hi, I'm curious as to where the some of the rows have gone? The index for the last row is 102158 where as the last row number is 102163, even if you take away 1 for the header row, 102162 it still doesn't match the index?

@gabbuzca Жыл бұрын

Thank you so much for your videos. My limit is 13000 rows, is it normal?

@zanodeloza2636 4 ай бұрын

This is Awesome. Thank you. Tried this and it works, however, I noticed from the 3rd loop (50000-75000 rows) that it messed up my data. For example, My column [account] has switched values from my [type] column. It only happens on the 3rd loop. 4th loop comes (75,000-100,000 rows) the data is fixed. Not sure what happened. I have a total row of 123k.

@hectorrojas1401 8 ай бұрын

I have an index because mu data is filteres and I cant use the full dataset for some dax expressions i created my final table has index but not complete because the filters, its like 2,3,5,7,8,12,16, can i run this code without problem using variables like you did?

@Psyresidente Жыл бұрын

Hi. How can we modify the query so users can download the information only for categories from the Slicers filters in the PBI.

@calvintanrio7126 2 ай бұрын

Hi, how to set dynamic filters in Run a query against a dataset that will followed from the slicer in dashboard? Thank you

@shwetasharma4844 10 ай бұрын

After exporting large data from this process, if we apply a filter, and then will it show export data according to it filter ???

@user-gn3cz3ix5n 4 ай бұрын

Thank you for the video,Is there any other way than this

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

Hi Curbal, Your video is really helpful for me. I need to implement it, can you please send the pbix file. I need to implement it for my client. I tried implementing the same way, but i am having issue with measure. so, can you please share me the pbix file, i want to view how you have implemented all the dax measures used in this video.

@JD-ff7cz Жыл бұрын

Hi, your videos are so useful, I've learnt so much :) One thing I cannot seem to solve is the repeating of column headers. I can't see a solution in the comments or power automate community. Could you help with this curbal?

@TheRatnakar2007 Жыл бұрын

I face the same issue.....Please help

@martinvanweel1126 Жыл бұрын

@@TheRatnakar2007 same here

@hichamfaidi Жыл бұрын

Hello @Curbal , I'm trying to export data from my Power BI dataset to a CSV file on SharePoint. My dataset contains over 1 million rows, and I've tried adding an 'index_' column using RANKX based on an ID column. However, when I export the data to a CSV file, I only get 2000 rows in the output. I need help with this issue. Can you please provide some guidance on how to export the full dataset to a CSV file on SharePoint? Thank you.

@sankey262 3 күн бұрын

Hi Great Job !! In my case last index came fine, but the total number of rows is missing. Could you help me with this?

@kimberlysuarez2571 Жыл бұрын

Please have a tutorial too for exporting on sharepoint list 😊😍

@sergioaranda3917 Жыл бұрын

hello, thanks for the video. My dataset have less than 50k rows but when it just export 34k rows without any filter, please help

@s.r.1997 5 ай бұрын

thanks! From what I`ve seen, it starts from the other video (100k rows extraction), i`m wrong? I mean, some code was already there and not explained so i guess it

@russellpetrie2956 Жыл бұрын

Great video, this will help me a lot. At the end where you combine into 1 csv, it looks like there are 4 extra rows added. The index is 102158 and the row number is 102163. Could it be the 4 joins are repeating a row?

@CurbalEN Жыл бұрын

It is the headers on each csv :)

@syedaamir8170 Жыл бұрын

@@CurbalEN Is there any way to filter out the headers?

@CurbalEN Жыл бұрын

Most likely, check with the power automate community

@mrmarcify Жыл бұрын

@@syedaamir8170 have you discovered how to remove the headers?

@rufmau68 Жыл бұрын

it works fine BUT unfortunally i cant trigger it from power bi bottom. Do you have suggestions..... if i use export csv, api create only a csv with 3000 rows on 8700 rows in total

@gabrieloliveiradequeirozmo5161 11 ай бұрын

Hello Curbal! Thank you once again for this great content. I have done this process but then I have come with one small question: every filter that is applied to the matrix/table on the report is ignored when generating the .csv file. It means that the export process ignores the filters applied to the report. Is there any way to solve it? Thanks in advance!

@CurbalEN 11 ай бұрын

Yes, check the playlist to see how add filters to the download.

@nishthatank5954 10 ай бұрын

hey please tell the which playlist@@CurbalEN

@smpa0137 Жыл бұрын

Smashing !!!

@CurbalEN Жыл бұрын

💥

@PriyankaKolanupaka 17 күн бұрын

Is index column mandatory? Is it like to have a row number for each row to make it unique? If so, can we use any ID column which is unique?

@CowCow517 Жыл бұрын

It's sound great. However, I don't know why I can't replicate your result. What I want to do is to export it by month (from Jan - Dec) and combine it into one CSV. Any hins?

@RL-br6el 3 ай бұрын

HELP!!!! First of all, this has resolved a significant chunk of my needs, so thanks for this a lot. So I've completed doing all the steps and I get to have the combined data into a single csv file. However, I have a set of data where many columns may have blanks, and realizing that Power Automate is not too kind with blanks, I noticed that when any loop begins with a row where any column has blank, those columns are getting skipped and hence pushed back to the right as the last columns in the output csv. Does anyone know how to avoid this? My initial thought is to replace blank values as one of the steps in the flow. I was trying to read on how Power automate can replace blank values but all the tutorials were about replacing the value for only one column. In my case, almost all of the columns may have blank values, hence I need the replace formula to apply for every column. As a newbie in Power Automate, I can't seem to do that expression correctly. Another option I thought was to replace the blank values in the dataset itself. However, my data is connected via DirectQuery hence transformation is limited. Anyone who knows the fix, appreciate if you can share. And please bear with me as I am still learning. :)

@ernestoandrerodriguez6496 3 ай бұрын

Hi, please how can I do if I don't have an index column? I have orders and I want to export the last 80 days periodically. I was thinking looping through dates but I could be possible that one day exceed the max rows allowed by batch :/

@aleksandrabysiec7477 Жыл бұрын

when I do the loop it appends data in different order- e.g. it appends columns ABC and in another loop it takes order BCD... Is there any solution for this?

@evama383 11 ай бұрын

I am having the same problem

@rlds07 Жыл бұрын

Excellent video. Congratulations! I need some help... How can I do it if I don't have the index column? Help me please!

@CurbalEN Жыл бұрын

I only added the index column to check that the download was done correctly. You don’t need it

@rlds07 Жыл бұрын

@@CurbalEN Thanks for the feedback

@rlds07 Жыл бұрын

@@CurbalEN Sorry to ask again Curbal, if I don't need the index column, how am I going to determine my loop? Can you guide me please?

@CurbalEN Жыл бұрын

It will depend on your data. Post sample data to the power bi community to get proper help ;)

@simonenovati7464 Жыл бұрын

Ciao, is it possible that you need to add 1 because you missed a range in the steps? It is missing the step between 50001 and 75000 because you have done directly 50001 to 100000

@CurbalEN Жыл бұрын

Maybe!

@irajoe9186 Жыл бұрын

Is it possible to export that size of data using power automate?

@ambatisrinuvasulareddy6345 Жыл бұрын

How to export different tables data form power bi in to one excel /csv using power automate at a time ex: i have 4 tables data needs to export one excel file with different sheets using power automate

@sanketchakane7745 Ай бұрын

how did you generated that index column can you pls share that link

@nooralfar10 Жыл бұрын

Amazing.. what if I don't know how many loops I need to do incase we have a real time data set? Can you do a vedio when the trigger is power bi button and we need to get data from power bi to an excel report (not table ex an invoice template)

@ScaphanNetwork Жыл бұрын

You could use empty(PowerBI data) which returns true or false. Then use that as your do until so that it carries on running until there’s no data to return. Although be careful because if you get the code wrong the do until will run for ever! If you click the 3 dots by the do until and open settings you can set the maximum times the do until runs. It defaults to 60.

@nooralfar10 Жыл бұрын

@@ScaphanNetwork great thank you I will try it on my data set

@SANJIVRAI6693 Жыл бұрын

The Column headers repeats for each loop and could be due to nulls the columns arrangement changes some gets missed but those should not happen if there data is of non-null records

@CurbalEN Жыл бұрын

Convert nulls to something else like zero or literal null and drop the headers. In the comments someone did it successfully so it is possible :)

@SANJIVRAI6693 Жыл бұрын

Yes that is not major issue, I skipped those columns currently. For now trying to get the header rows deleted. Maybe filtering out the non numeric from index column is easy to achieve.

@Railfan727 Жыл бұрын

How do I determine if there is a syntax error in the query code? Also, is it possible to accept user inputs to pass into the filter criteria in the query? For example, to prompt for the start and end of a date range...

@CurbalEN Жыл бұрын

Check the playlist with all the videos, I cover everything in detail there.

@Railfan727 Жыл бұрын

@@CurbalEN I found it. I do have a question though. I'm trying to use a min() expression to pull out the min of a field... min([SHIPPINGDATEREQUESTED])... the editor is refusing to accept that as valid input... if I click on "Update" it doesn't do anything but if I replace the field name with a constant it accepts that... but WHY??? I do not understand how the syntax on this is supposed to work. Thank you.

@anuragsingh711 9 ай бұрын

what if we don’t have index column in data source any way around that.

@victormanuelsandovalarias4630 Жыл бұрын

Hello I have problems to export data when I published the Report. The button gets stuck and don’t work 😢

@landchennai8549 Жыл бұрын

How to connect tenable vulnurabilities through power BI

@mh2734 Жыл бұрын

This is great video. However when I tried it for some reason the structure of the columns is not the same in the each batch (loop), which result in the data structure that cannot be used.

@strike497 Жыл бұрын

If you use custom columns for the Create CSV Table action this can be worked around. Not ideal if the dataset structure is likely to change but good for some applications.

@mh2734 Жыл бұрын

@@strike497 finally I found out that it was caused by empty / null values. I replaced them with "0" or "n/a" and it helped

@manchitas997 Жыл бұрын

Great video, i was wondering if i can make a table with combine cells, lets imagine we want a report for every sell we got this month, with name of the customer, product and an unique id, lets same a customer buys 10 things, i will like to see it with a combine cell instead of seeing 10 times de customer name, that would make it more easy to reed

@maxstripp1224 Жыл бұрын

You can create a calculated table in the model for that. And query that table

@kiterbram Жыл бұрын

Great video! However while testing my Flow I received a 'Flow run timed out" message after 10 minutes. How can that be fixed?

@CurbalEN Жыл бұрын

I dont know, paste the details on the power automate community, maybe someone knows?

@hichamfaidi Жыл бұрын

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

Can I use this approach if I don't have a index column nor permission to make one in my dataset. In run a query against a dataset part.

@ch.shravya2040 6 ай бұрын

Do you find any solution

@user-tl8kl1lb4y 6 ай бұрын

@@ch.shravya2040 no not yet trying with power automate and excel

@kudhal_bader2688 Жыл бұрын

the problem is PowerBI Run Query will exclude the column if entire column is null or blank, appending above array will skip the column and in some scenario this will create the misaligned columns. I have tested this and getting above error in my data, do you have any suggestion ?

@CurbalEN Жыл бұрын

Substitute blanks to zeros and give the columns names before exporting in power automate

@rizwanshaik660 9 күн бұрын

I am able to get the data but it its not respecting the RLS rules, its overriding the rules and exporting all rows in the table, can you please help me, what additional steps are needed to achieve this, Thank you very much

@hectorrojas1401 8 ай бұрын

I followed eveey single step, it runs well, but the files in teh folder contains the same information, i have 9000 rows, it generates two files of 6000 rows because is mi limit and both contain the same info, the last 3000 rows is not being pulled, what im doing wrong???

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

Hi, While appending CSV's I don't know why but my columns are changing positions, any suggestions?

@balasubramanya-em2or 7 ай бұрын

Did you find any solution. I am facing similar issues

@harshchovatiya-gh7hn 5 күн бұрын

i follow your each steps but my final output data have a duplicate rows. can you please guide?

@piotr5517 Жыл бұрын

My flow works like on the recording, but I really need to finally save the file to Excel (.xlsx). How to do it? Please help me 🙏

@CurbalEN Жыл бұрын

I show in one of my other videos how to export to excel. Check it out on the export playlist.

@brief001 Жыл бұрын

I've seen several comments from readers that they don't know how to apply this logic if you don't have an 'INDEX' column available. In your response I keep reading "You don't need the id to export the rows, I used it only for checking". But with code 4, a filter is applied to this 'INDEX' column at the first VAR. How should this VAR be adjusted if you don't have an 'INDEX' column?

@CurbalEN Жыл бұрын

It depends on how your data looks like. See if you have any other column you can use.

@brief001 Жыл бұрын

@@CurbalEN I am using a calculated table, and it has no INDEX column. I was already looking on the internet if I can add an INDEX column, but I didn't find a solution. But maybe you have a 'trick' to add an INDEX column to a calculated column. (And then not afterwards by adding a calculated column to the calculated table).

@ericholdforth4981 Жыл бұрын

Hi Bas, Did you have any joy with this at all. I'm in a similar prediciment. All that is happening when i remove the filter is the same data is being populated into each file.

@brief001 Жыл бұрын

Hi @@ericholdforth4981 I solved it quickly now by creating the query table in the Power Query editor, applying the filtering there and adding an index. Only now unnecessarily duplicate data is being read into my data set, so this alternative is not desirable. I'm still looking for a way to add an index column in DAX when creating a new calculated table via DAX.

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

Hello, did you find a solution? @@brief001

@irajoe9186 Жыл бұрын

Hello Curbal, I followed all the steps in the video but one I run my flow I got the error saying: PowerBI query error “the query referenced calculated column XXX which does not hold any data because evaluation of one of the rows caused error”. I am exporting over 700k rows. Any help will be appreciated. Thanks

@CurbalEN Жыл бұрын

My guess is that the DAX query is failing. Post in the power bi community the details to get help !

@1412chinky 8 ай бұрын

Were you able to resolve this issue. I have same error

@ruchikalomite1068 19 күн бұрын

How can I attach this PBI export in an email and send ? Could you please help me with that?

@jerometurmel4208 Жыл бұрын

Well tought :)

@CurbalEN Жыл бұрын

Glad to hear, it took me forever to shoot!! :)

@jerometurmel4208 Жыл бұрын

@@CurbalEN One quick question, won't your title be repeated in your appended csv file since your do until always come with title.

@CurbalEN Жыл бұрын

Yes it will. For us it wasn’t a problem,, but I am sure if needed you should be able to drop it and add it manually.

@FelixHenriksen Жыл бұрын

@@jerometurmel4208 since there is a index on value row, a remove dublicate will take care of the extra titles. Not sure if that function exist in Power Automate.

@kudhal_bader2688 Жыл бұрын

@@CurbalEN yes i did it, i used one more variable to append header less csv to second one which is AppendCsv2 along with header details. this worked very well, thank you so much @curbal for making us amazed always. this requirement was just in time with your video :D

@iuliacucos329 11 ай бұрын

Hi, I have tried running this for more than 2million rows and everything is good until I reach Do Until where after 11 minutes I get an error “Flow run timed out.Please try again”. I have adapted the Do Until settings to have a Count=1000 and Period is blank (not sure if that is right a bit confused about the parameters). Would you happen to know what I might be doing wrong or a way to improve this? Any help is greatly appreciated!! Thank you

@CurbalEN 11 ай бұрын

Maybe there is a limit for how long a flow can run? If that is the case filter the data and run it in separate flows.

@iuliacucos329 11 ай бұрын

@@CurbalEN Thank you, I will have a look