You can also directly import semantic model and data flows into excel from Data > Power Platforms, which shall create a connected table or pivot in excel.
Thanks a lot. Can you link data to Excel's data model directly so the end user can use dax on local machine without using power query because of much data?
@@AccessAnalytic Thanks. Actually.... I played with it and got it connected directly into data model form the BI table. I don't have power query at all but have a power bi connection to powerpivot. Is there any resource here to show screenshot?
So I’m not quite following. Are you connecting to a Power BI data model that lives in PowerBI.com or are you connecting to a Power Pivot data model that lives in a local Excel file.
@@AccessAnalytic Here is the protocol: 1. In the PowerBI Destop: Enable that report can export underlying data (CURRNET FILE > Report Setting, Export Data) Make a simple table with a Sum measure (two columns - date and measure (Sum of Values)) Upload Dataset to the PowerBI Service 2. In PowerBI Service: Click on table and export to Excel "summarised data with leave connection" 3. In Excel: Find this connection (under Data & Connections) and copy connection string to any text editor Edit this string to remove statement 4. In this or any other Excel file: Open Data Model in Excel Select Get External Data > From Other Sources > Others (OLEDB/ODBC) Paste Connection String and Test Connection In the MDX field Enter DAX statement starting with EVALUATE for the dataset table. If table name is tb1, the statement that brings all the data to the Data Model will be EVALUATE tb1. Alternatively one can do other filters and functions as FILTER, CALCULATETABLE, SELECTCOLUMNS, etc Confirm with Finish. Now refreshable data is in the Data Model and can be used for reports in Excel. Please let me know if it works for you.
Brilliant video, thanks Wyn. But this does not work in my case. I want to export underlying data from a power bi report link shared with us from third party (our client). We are not the owner of the report and I cannot login to the worskspace. But our client enables the underlying data downlaoding by us. Right now I can only download (export) the underlying data manually from the visual on hte power bi report link. Is there a way to download the underlying data automatically using power query? Please kindly advise.
Greetings I have created a new table on power bi containing around 17m records .. How to export it as csv file Or can i analyse on it on power bi again
Can we retrieve the huge table records using power automate? Because i have a table in PBI consists of 45000 records. Under DAX query i have written Top 50000 but in CSV i am getting only 13000 records. Is this a limitation? Or do we have a other way to get the table data using power automate?
Great videos and content as always.. I need help badly.... I have a use case where I am getting data from matrix visual in Power BI report. I want the functionality once the button clicked (Power Automate in Power BI) and I want dynamic data which is showing in my matrix visual in Power BI to be exported to excel (the number of dimension/categorical fields are dynamic based on "Field parameters (which are dimensions not measures)" like sometimes the user will select 4 or sometimes 5 but max 8 and numeric or measure fields are static and there are around 10. Here is the main thing, the data should be exported to "Excel" in a hierarchical fashion (Parent and Child), should be like: Name Value1 Value2 Parent 100 200 Child1 100 200 Child2-1 50 100 Child2-2 50 100 Is it possible using Power Automate. Any suggestions or help are greatly appreciated. Thanks in advance.
Loved the live connected/refreshable table. Never knew that was possible.
Cheers Emily!
This is valuable information. Thanks Wyn, for providing all these options!
Cheers Iván.
You can also directly import semantic model and data flows into excel from Data > Power Platforms, which shall create a connected table or pivot in excel.
Yep absolutely !
Brilliant. Thanks Wyn!
You’re welcome 😀
Great tips , I’ll start using the addins in power bi , specially bravo
They do open a lot of extra functionality
At 7:39 you can see the data export from a visual, anyone here know why this option on visuals has been removed?
It can be disabled by the report creator
Thanks, but will we able to see the export option in the published link?
Not sure what you mean sorry
@@AccessAnalytic After embedding the report into a website, will the export to excel button be visible for a table visual?
@DeepakNair-l7r not sure if embedding, but works if accessing in PowerBI.com
Thanks a lot. Can you link data to Excel's data model directly so the end user can use dax on local machine without using power query because of much data?
You can’t connect to a data model in a separate Excel file, but can create the table connection inside the same file as the model
@@AccessAnalytic Thanks. Actually.... I played with it and got it connected directly into data model form the BI table. I don't have power query at all but have a power bi connection to powerpivot. Is there any resource here to show screenshot?
So I’m not quite following. Are you connecting to a Power BI data model that lives in PowerBI.com or are you connecting to a Power Pivot data model that lives in a local Excel file.
@@AccessAnalytic Here is the protocol:
1. In the PowerBI Destop:
Enable that report can export underlying data (CURRNET FILE > Report Setting, Export Data)
Make a simple table with a Sum measure (two columns - date and measure (Sum of Values))
Upload Dataset to the PowerBI Service
2. In PowerBI Service:
Click on table and export to Excel "summarised data with leave connection"
3. In Excel:
Find this connection (under Data & Connections) and copy connection string to any text editor
Edit this string to remove statement
4. In this or any other Excel file:
Open Data Model in Excel
Select Get External Data > From Other Sources > Others (OLEDB/ODBC)
Paste Connection String and Test Connection
In the MDX field Enter DAX statement starting with EVALUATE for the dataset table. If table name is tb1, the statement that brings all the data to the Data Model will be EVALUATE tb1. Alternatively one can do other filters and functions as FILTER, CALCULATETABLE, SELECTCOLUMNS, etc
Confirm with Finish. Now refreshable data is in the Data Model and can be used for reports in Excel.
Please let me know if it works for you.
Excellent! Thank you!
You’re welcome.
Brilliant video, thanks Wyn. But this does not work in my case. I want to export underlying data from a power bi report link shared with us from third party (our client). We are not the owner of the report and I cannot login to the worskspace. But our client enables the underlying data downlaoding by us. Right now I can only download (export) the underlying data manually from the visual on hte power bi report link. Is there a way to download the underlying data automatically using power query? Please kindly advise.
Hi, thanks. No the owner is in control of the data so they’d need to set up the export for you.
Greetings
I have created a new table on power bi containing around 17m records ..
How to export it as csv file
Or can i analyse on it on power bi again
If you create a Dataflow then you can re-use it in multiple reports. Per the Video BRAVO or DAX STUDIO or Paginated report
Can we retrieve the huge table records using power automate? Because i have a table in PBI consists of 45000 records. Under DAX query i have written Top 50000 but in CSV i am getting only 13000 records. Is this a limitation? Or do we have a other way to get the table data using power automate?
Sounds more like your export is grouping data. 13,000 isn't a limit I've heard of
Great
🎉
Great videos and content as always..
I need help badly....
I have a use case where I am getting data from matrix visual in Power BI report. I want the functionality once the button clicked (Power Automate in Power BI) and I want dynamic data which is showing in my matrix visual in Power BI to be exported to excel (the number of dimension/categorical fields are dynamic based on "Field parameters (which are dimensions not measures)" like sometimes the user will select 4 or sometimes 5 but max 8 and numeric or measure fields are static and there are around 10.
Here is the main thing, the data should be exported to "Excel" in a hierarchical fashion (Parent and Child), should be like:
Name Value1 Value2
Parent 100 200
Child1 100 200
Child2-1 50 100
Child2-2 50 100
Is it possible using Power Automate. Any suggestions or help are greatly appreciated. Thanks in advance.
I'm not too sure sorry
I'd recommend posting to
www.reddit.com/r/PowerBI/
or
community.fabric.microsoft.com/t5/Service/bd-p/power-bi-web-app