Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I was able to export a dataset in Power BI in desktop using the R script but apart from the columns that are present in the dataset, how do I also export calculated columns and measure using R Script. See screenshot below.
Solved! Go to Solution.
@Kolumam , Good Morning!
I will say again, and trying to make it simpler to understand.
First you can't export in any way possible measures in Power Query (Even if you use any kind of external tool.).
Thats because Power Query work with the database queries only.
After you ETL your base in Power Query, the Power Pivot takes part, and just At this moment a measure start to exist.
So, if you need to export the result of your measures, it can only and only be made in the Power Pivot part of Power BI.
Ok, with this clear, lets extract your base with the result measures in a very, but very simple way.
See the Picture attached to follow:
Start from Up Left ok!
Let's just make some attention points:
But if I made a mistake and you need to export just all the measures created (the formula of all your measures), you need to get the amazing tool from sqlbi.com "Dax Studio".
After you install the DaxStudio close it.
Open your Power BI/Excel file/Tabular Server
Open again DAX Studio
You be prompted to connected to your opened file
Choose the correct one (In this case will be the middle one PBI / SSDT Model)
Input the code bellow:
select * from $SYSTEM.MDSCHEMA_MEASURES
Click in Run and bellow in results a table will appear with all the measures created like the pic bellow:
With this tool you will be able to extract a lot of content from your pbi file, run queries, see summarize results in a simple way, start work to make your PBI more fast, etc.
See if it work or is the result need to you.
Forgive me if I am being extremely simplistic in the answer, but would not it be easier simply if you entered in the DATA menu in the left menu of Power BI (not Power Query), click on the desired table with the right mouse and choose to export entire table?
To export with the measures created, just input that measures in columns.
If you need to export more than one table, create a virtual table with "Join", and then insert the measures in columns and you get the result needed.
As @TomMartens say, measures is not present in query in any ways, they are created in a instance outside Power BI (in Power Query).
What I am trying to do here is an auto export of data to a csv using R script. Currently, I am able to export only the dataset but not the measure columns. @Anonymous @TomMartens
How to export the dataset along with calculated columns and measures?
Hi @TomMartens @Anonymous
Any other alternatives we have here using integration with office 365 or something else?
Regards
Krishna
@Kolumam , Good Morning!
I will say again, and trying to make it simpler to understand.
First you can't export in any way possible measures in Power Query (Even if you use any kind of external tool.).
Thats because Power Query work with the database queries only.
After you ETL your base in Power Query, the Power Pivot takes part, and just At this moment a measure start to exist.
So, if you need to export the result of your measures, it can only and only be made in the Power Pivot part of Power BI.
Ok, with this clear, lets extract your base with the result measures in a very, but very simple way.
See the Picture attached to follow:
Start from Up Left ok!
Let's just make some attention points:
But if I made a mistake and you need to export just all the measures created (the formula of all your measures), you need to get the amazing tool from sqlbi.com "Dax Studio".
After you install the DaxStudio close it.
Open your Power BI/Excel file/Tabular Server
Open again DAX Studio
You be prompted to connected to your opened file
Choose the correct one (In this case will be the middle one PBI / SSDT Model)
Input the code bellow:
select * from $SYSTEM.MDSCHEMA_MEASURES
Click in Run and bellow in results a table will appear with all the measures created like the pic bellow:
With this tool you will be able to extract a lot of content from your pbi file, run queries, see summarize results in a simple way, start work to make your PBI more fast, etc.
See if it work or is the result need to you.
Hi there,
with the new performance analyzer it is actually easier than ever to export measure (values) from Power BI to a csv. I've written it up here: https://www.thebiccountant.com/2019/07/28/export-large-amount-of-data-from-power-bi-desktop-visuals/...
This method goes via DAX-studio, but you can go one step further and connect to your current PBI Desktop model from the query editor and read those things from the current model using this trick:
https://www.thebiccountant.com/2016/04/09/hackpowerbi/
Use the automatically created DAX-script as the function parameter of the function posted in the article.
then use your R (or Python) script to export to your csv of choice. This enables you to run multiple queries at once (although it is my impression that DAX Studio is much faster)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |