SCHEDULE Exports from Power BI AUTOMATICALLY // Power Automate to SharePoint Folder Data Exports
Вставка
- Опубліковано 27 вер 2022
- 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/solutionsabroad/shop
solutionsabroad.co.uk/store
❤ Other ways to support
ko-fi.com/solutionsabroad
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 UA-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.microsoft.com/en-us/d...
#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
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!!!
Great knowledge 👍 Your videos are very helpful. Thanks
Thank you very much. Amazing video! Helped to solve my current problem.
Your tutorial very good i am from Brazil. Thank you!!!
this will also give the latest data export from power bi service?
This is brilliant, thank you!
Thank you so much! It helped me at work a lot! Best of luck with channel promotion!🎉😊
Great video! Thank you!
Great job! Thank you!
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
Thank you so much. Easy explanation 💯
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?
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?
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).
Love it, thanks!
Very good, thank you.
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.
Hi. If I wanted the trigger to be based on the dataset refresh timing, how would I do that?
saved my day , tks :)
helpfull.. great video
Is it possible to export measures as well? Or only columns? Thanks
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.
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!
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 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.
Can we export the Matrix Visual from Power BI/Power Bi report builder into share point automatically?
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
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
Hi Fernan, can we do this for Usage metrics report as well? If yes, could you briefly paste the solution for reference?
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
This is the best thing since sliced bread.
Is this possible to do with dynamic RLS as well? :)
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
this will also give the latest data export from power bi service?
Thanks It worked but it do not return all records from PBI... i tried all methods... is there any alternate way
Hi, How many rows are allowed to export with this option?
Is there any way to export data in excel from PowerPoint app online with power automate??
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?
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!
can we export measures column aa well
Thanks Great!
How can we export data from Power BI to Excel automatically and send it to the client through the mail using scheduling?
How many times per day could I run this flow?
Thanks!
Can we get json data file from pbi datasets through power automate
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
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
I can confirm this works
How I can export data in excel from power bi app using power automate??
How can I format the dates in my table?
I tried but more than 100K data not exporting ,
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.
Is first table rows unlimited? br
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.
Hi thanks, however file is limited to ~7000 rows
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?
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
Show how to get into xlsx or xlsm.
Hi I tried exporting a table with 20000 lines . This method returned only 14000 lines
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.
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.
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 :)
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 want to export all data from the table automatically, how to do that?
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
How to remove T and format Order Date in date column?
This is also exporting limited number of rows only
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 ?
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
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 😢
How to use the power automate action - "export to file for a paginated report"
Flow ""run a query against a dataset 4:25
13:11 filter function
Is this for free?
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
max data downloaded is 100000 in csv using this
This is not relevant for most organizations. Show us how to do it as scheduled flow and not by pressed button !!
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?
this will also give the latest data export from power bi service?
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