SCHEDULE Exports from Power BI AUTOMATICALLY // Power Automate to SharePoint Folder Data Exports
ฝัง
- เผยแพร่เมื่อ 28 ก.ย. 2024
- In this video were going to go through how you can use Power Automate to export data from Power BI reports.
-
🚩 Get Demo Files here
bit.ly/3dJE2O7
👍 Become a Patreon to support the channel and get free perks!
/ solutionsabroad
🛍 Get exclusive templates built using best practices here
ko-fi.com/solu...
solutionsabroa...
❤ Other ways to support
ko-fi.com/solu...
GET IN TOUCH
📺 Website - www.solutionsabroad.co.uk
🤵 LinkedIn - / solutionsabroad
📘 Facebook - / solutionsabroad
🌍 WHO IS SOLUTIONS ABROAD
Hi, my name is Fernan, the one-man band of Solutions Abroad. I work as a Data Scientist based in London, UK and have over 6 years of experience working in Business Intelligence. Solutions Abroad these days is dedicated to teaching the ins and outs of Power BI, through the power of TH-cam.
If you want to learn more, I’ve conveniently created playlists to help you find the right topic for you.
🐎 First, watch this video about how a typical Power BI workflow looks like
• Power BI from START to...
🗺 Learn DAX to extend your Power BI calculations
• Power BI DAX Basics OLD
📆 Stay up-to-date on all the features Power BI releases monthly
• Power BI Monthly Featu...
🧪 Get certified as a Microsoft Data Analyst Associate
• Power BI DA 100 Prepar...
💡 Learn Power BI Features to improve your dashboards
• Power BI Feature Highl...
📊 WHAT IS POWER BI?
Power BI is a business analytics tool by Microsoft. It creates a working environment suited for normal users, meaning tasks such as data extraction, cleansing, analysis and storytelling; these are made easily-accessible to citizen-users, and extendable for technical users. At face value it provides visually-stunning, interactive dashboards, but it’s built on top of so much more.
🔻 Get started with Power BI Desktop for FREE
powerbi.micros...
#PowerBI #DataAnalytics #BusinessIntelligence
FAST WORKAROUND FOR ALL OF YOU -
1. In PBI create a raw table with data, no title, not matrix view, add all the columns you want from tables, measures, etc.
2. PBI click View at the top then Performance Analyzer
3. Then Refresh Visuals
4. Select your table in the list and expand it with the + symbol in the performance analyzer window
5. Click Copy query and paste that into your Power Automate query text section
Done :)
Man. Who the Fuck are you. You don't know how much good you did today. Thank you.
@@nagarjunaambati3141 you are welcome lol.
Just came back to say thank you. You're the man
@@GIbrAvila I have been using this method recently to also create complex DAX. Add your filters to the table, etc. Then run performance analyzer and grab the code, copy paste and modify... Great stuff and glad I could help!
@@rickcoterie5700 how much do you modify and what? I keep getting errors when copy pasting the complete formula
Thanks a lot for the information Fernan! Really useful and much appreciated!
Thanks a lot for the information provided, I have one inquire.
Could I export the data in an XLSX format instead of CSV, and how?
I’ve needed to be able to do this for sooo long. Thanks so much!
Boss is very happy with me after using this tutorial. Thank you!!!
Thanks a lot for this video !
Great video and clear. It REQUIRES access to the workspace of the PBI report, however. I have many reports where I have read access in Cloud PBI but I do have access to the Workspace. How do I automate export from a PBI report without Workspace access?
Your tutorial very good i am from Brazil. Thank you!!!
this will also give the latest data export from power bi service?
Great knowledge 👍 Your videos are very helpful. Thanks
Is there a way to format the headers in the csv file automatically within Power Automate to not have the square brackets?
I am facing the same issue.
Have you got it?
Thank you very much. Amazing video! Helped to solve my current problem.
Can we setup the schedule the export in customized calendar ? As we know, sometime users would like to see the report in some specific day such as BD1, BD2, etc..and avoid the holiday.
So it would be great if the export could avoid holiday or happen with a customized calendar.
Wow Fernan! Thank you very much, your explanation and video helped me a lot. I just have one question, how do I query 4 tables? because I tried generating 4 separate queries and it works. But I would like those 4 tables to be together so I can send them by mail! Thanks You!
How to remove those brackets at the column name while automating the process.
Hi @indranildas_55_a70 add in extra 2 steps into your flow with Replace command. It should look something like this: Replace(output/powerbi first row column, ‘text you want to replace’, ‘’)
Repeat this 2 times first for start of the bracket then second for end of the bracket. And use output from second compose to create csv.
Names in each of the csv file header columns are transferred within square brackets [ ], is there anyway of changing this so the header names are not in brackets?
That's a great one, I'm not sure but let me come back to you on that
@@SolutionsAbroad did you find a solution ?
I new version on Power BI Desktop is a Work with DAX query view funcionality where is possiblity to create and evaluate dax code . After that you can copy query to flow
Hi, do you you know if we can get rid of the [ ] on the header? thank you
Hi Natalia, I'm not sure but I will have a look and let you know!
@@SolutionsAbroad thank you :)
I have the same issue, did you solve it ?
Hi Fernan, can we do this for Usage metrics report as well? If yes, could you briefly paste the solution for reference?
Thank you for the video, but I have one question:
When I do create this flow the same way you did (step by step) the .csv file will display my data in just one column, even though the original table has multiple columns. How can I fix this?
Thank you for your help!
I want to be able to export data from my PowerBi to Google sheets directly without having to save in excel and then paste in Google sheets. Can you share a process that would help in automating this and if you could share it on your channel it would be helpful.
Hello, I currently have a Visualization (table) within a dashboard in Power BI Service that captures the snapshot of live data each day (via incremental refresh). However, I figured that when I follow your steps, the "Run a query against a dataset" always seem to cause an issue. Is there a way for me to auto-export those incremental refreshed results that are in the dashboard in Power BI Service?
Thank you so much! It helped me at work a lot! Best of luck with channel promotion!🎉😊
Hi,
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.
Very good, thank you.
this will also give the latest data export from power bi service?
How can I format the dates in my table?
Hi I tried exporting a table with 20000 lines . This method returned only 14000 lines
After running the query against the dataset, is there a way to use that info to populate a SharePoint list (rather than turning it into a CSV and putting it in a SharePoint folder)?
Haven't seen an answer to this query - would be really helpful if anyone knows? It seems possible to populate an SPO list using a PowerAutomate button in PBI to export to an SPO list but no obvious way todo if its a scheduled export
I want to export all data from the table automatically, how to do that?
Thank you so much. Easy explanation 💯
I am actually trying to export the data of a report (specific visual : table, not all other parts of the report) in PBI service into excel and email it directly to the asking person using power automate but it keeps failing or sending an email without an attachment! I really need help please
Hi :) Did you figure it out maybe? It seems like the same case I'm working on now and trying to find a working solution :)
If we use this approach we cannot overwrite. Next day it does not overwrite at all as the file is with the same name. And when you test it shows the error of having same file name
how to remove the bracket [ ] in the header column
Hi, How many rows are allowed to export with this option?
In dax studio, after running the query i am getting 33,000 rows but, when i pasted the same query in automate in csv file only getting 4,000 rows. How is this possible? I am stuck, please help
helpfull.. great video
Hi, do you know if i did something wrong, when i run the flow, its error as the file.csv already created. You had mentioned if file exists it will overwrite the old file. is this correct? thank you
Thanks Great!
Is this possible to do with dynamic RLS as well? :)
Great video. Thank you. I have a table visual that shows all outstanding orders line by line. Along with Requesters name and his/her email address. Is it possible to create a flow where email is sent to every order requester with a list of all outstanding orders lines I have in my visual? it's kind of mail merge but with a table of all outstanding orders line by line.
How many times per day could I run this flow?
So you have to use DAX in the Query Text prompt?
You can't just refer to a table created by Power Query?
Thanks.
You can, just right Evaluate and all the columns you have in power query table and you are good to go.
How I can export data in excel from power bi app using power automate??
sadly it does not overwrite fille as you mention @10:46 but it gives me error A file with the name "xxxx.csv" already exists .
I am also facing the same issue . I want to publish back the previous data every week to know the changes. Is there any other way this can be achieved
Show how to get into xlsx or xlsm.
This is also exporting limited number of rows only
Is first table rows unlimited? br
Nice video ! Thanks ! Do someone know if there is a way to convert dates format to DDMMYYYY instead of the default ISO format in the CSV file? For example 29/11/2017 instead of 2017-11-29T19:13:17.5844778
Only use the DAX function FORMAT in your query
seems texts in languages other than English will be return error in CSV. can fix? /(ㄒoㄒ)/~~
Is this for free?
Nice work Fernan, I enjoyed the video as always, but please when i tried to run my flow i got the following error
{"error":{"code":"DatasetExecuteQueriesError","pbi.error":{"code":"DatasetExecuteQueriesError","parameters":{},"details":[{"code":"DetailsMessage","detail":{"type":1,"value":"Query (14, 1) The syntax for 'Evaluate' is incorrect. (Define
VAR _table = SelectColumns(
\t'MetricsProjects'
\t,\"Project #\", 'MetricsProjects'[Project # Full]
\t, \"Delivery Manager\", 'MetricsProjects'[Delivery Manager]
\t, \"Project Region\", 'MetricsProjects'[ProjectList.Region]
\t, \"PO #\", RELATED('Unreceived'[DocNum])
\t, \"Line #\", RELATED('Unreceived'[L#])
\t, \"Exp Type\", RELATED('PMO TaskGroup'[Exp Type])
\t, \"Vendor\", RELATED('Unreceived'[Vendor]
\t, \"Missing OLR\", [Latest Missing OLR]
\t, \"Days\", RELATED('Unreceived'[Days Old])
)
Evaluate _table)."}},{"code":"AnalysisServicesErrorCode","detail":{"type":1,"value":"3238920194"}}]}}}
Please what have I done wrong?
Instead of rewriting the whole table, you could just go to Performance Anlyzer, start Recording, copy query, then paste into Power Automate query text. Done.
That's a great solution! I think this is also a good alternative if you don't have access to Power BI Desktop
Just a heads up, this approach still suffers from truncated exports. An approach I've gone for is to identify what columns I can partition my data on, and then iterate over those partitions doing smaller exports that I am confident will be under 1000 rows (although it's not strictly 1000 rows, lots of columns also factor into it).
I tried but more than 100K data not exporting ,
How can we export data from Power BI to Excel automatically and send it to the client through the mail using scheduling?
Nice video! Does anyone know if it's possible to export like this from Power BI filtering from [Order Date] just by values corresponding to the current year and month (or the previous month)?
see my comment above. Should cover what you needed if you didn't solve it already. In the filters add your specific dates and do what I listed above to get the code.
I am so impressed. CSV file's field names has []. I would like to remove these symbols. How shall I do?
this will also give the latest data export from power bi service?
This is not relevant for most organizations. Show us how to do it as scheduled flow and not by pressed button !!
How to remove T and format Order Date in date column?
Can we get json data file from pbi datasets through power automate
How to use the power automate action - "export to file for a paginated report"
max data downloaded is 100000 in csv using this
Nice video ! But when I tested my Power Automate I have an error : "{
"error": {
"code": "PowerBIFeatureDisabled",
"pbi.error": {
"code": "PowerBIFeatureDisabled",
"parameters": {},
"details": [
{
"code": "DetailsMessage",
"detail": {
"type": 1,
"value": "'DatasetExecuteQueries' is not enabled for the tenant."
}
}
],
"exceptionCulprit": 1
}
}
}"
Thanks for watching! From that error it looks like your tenant admin has disabled this feature, try to speak with your IT Admin (if you have one) and they should be able to help you
saved my day , tks :)
Is there any way to export data in excel from PowerPoint app online with power automate??
This is a great tutorial!
Question:
How can I set up a schedule to export data to the same file in Power BI, ensuring that new data is appended to the existing file rather than updating it? I want to avoid losing any previously extracted data that may no longer be present in the original source.
Nice video. What if I wanted to schedule exports from Power BI automatically to a SharePoint List instead?
Amazing.. this video helped me a lot. thanks.. I have a question.. I use the command CALCULATABLE in the query and I need the data Sorted. Somebody know how generate it sorted?.. thanks in advance
Very good video - Is there a way to create a Scheduled Flow that runs once a month and exports Power BI data to XLSX file? As of now I can only do that using a button alternatively if I create a Schedules flow I can only export the data in this manner you showcase here but need XLSX format.
I am only getting some of the records, my current table has around 74,000 rows but only about 21k+ are being added to the CSV file why?
If we want flow to gives us output to specific duration say last 7 days only, how to do that, as we are coping query from PowerBI directly which has date range and power automate does not change date dynamically
Can we export the Matrix Visual from Power BI/Power Bi report builder into share point automatically?
Thanks It worked but it do not return all records from PBI... i tried all methods... is there any alternate way
If the report contains two pages how we can export in csv and the thing in a single csv file two pages have to be attached
Hi thanks, however file is limited to ~7000 rows
Hi. If I wanted the trigger to be based on the dataset refresh timing, how would I do that?
Is it possible to export measures as well? Or only columns? Thanks
can we export measures column aa well
This is the best thing since sliced bread.
I can confirm this works
Great job! Thank you!
Thanks!
This is brilliant, thank you!
Great video! Thank you!
Love it, thanks!
Flow ""run a query against a dataset 4:25
13:11 filter function
Great video, a question please: how do run it with "Dynamic slicers/filters" because each time the user may change the inputs so i need the query to be more "dynamic" how to do that ?
Still awaiting for same 😢
Great Video!! is there a way to export the table and email as an excel attachment on a scheduled basis?
Yes we can do just add Send an Email Step