cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kolumam Member
Member

Measure column export using R script

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.Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
diogoblunck Regular Visitor
Regular Visitor

Re: Measure column export using R script

@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:Sem título.png

 

Start from Up Left ok!

 

  1. Enter in your data form the first icon pointed in red.
  2. Chose the table you need to extract and create the measure column.
  3. Click in the modeling tab > Create New Column
  4. Give a proper name to your column and insert after ":" the measure you created > Press "Enter"
  5. You will see the measure applied to all your rows of your table.
  6. After this just right click in any part of the table and choose "export all table"

 

Let's just make some attention points:

  • Not all measures works the same way for visuals and for tables.
  • Some adjusts may be need to your measures if they don't work as expected, but not that hard and you can always come back here for help 😃
  • This work with one table at time, so if you need to export more than one table, first create a join table (You can chose do this or in power query or with dax)
  • If you see, thet steps above are made by hand, and not automatic, but you can take some more steps within the Service (Power BI online - app.powerbi.com)
    • After you created you BI and input the measure columns, publish your bi.
    • In Service (app.powerbi.com) you can make an analysis on Excel,
    • just click in your report section
    • On the right side of the name of your report click in the [...].
    • Chose the option Analysis in Excel.
      • You will be prompted to install an add-on, and after you can open the file
    • Open the excel, and insert your power bi service credentials
    • now you get a pivot table with all the tables and measures created
      • Every time you update the pbi file  and publish, you can update the pivot table in this file
      • in excel its a lot easier to created macros for export your data 😃

 

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:

Sem título2.png

 

 

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.

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Measure column export using R script

Hey,

I guess this will become difficult, as measure will not be "present" in the query editor. As measures will be added to the data model at a later stage.

You can try to add a line into a r visual script that exports the dataset. Create a measure that just allows to toggle between true and false. Add all the needed columns to the visual including the measure. Toggle the measure using a slicer and watch the given path 🙂

This is just a guess, so you have to try it

Regards,
Tom

If your dataset resides in a workspace premium capacity you can use an xmla endpoint to connect to your dataset. create a linked server and some sql to export the data
Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
diogoblunck Regular Visitor
Regular Visitor

Re: Measure column export using R script

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).

Kolumam Member
Member

Re: Measure column export using R script

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. @diogoblunck @TomMartens 

 

How to export the dataset along with calculated columns and measures?

 

Super User
Super User

Re: Measure column export using R script

Hey,

an automatic export of calculated columns and measures will only be possible using the feature "xmla endpoint". This feature is availble to workspaces that reside in a premium capacity.

Regards,
Tom
Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Kolumam Member
Member

Re: Measure column export using R script

Hi @TomMartens @diogoblunck 

 

Any other alternatives we have here using integration with office 365 or something else?

 

Regards

Krishna

Highlighted
diogoblunck Regular Visitor
Regular Visitor

Re: Measure column export using R script

@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:Sem título.png

 

Start from Up Left ok!

 

  1. Enter in your data form the first icon pointed in red.
  2. Chose the table you need to extract and create the measure column.
  3. Click in the modeling tab > Create New Column
  4. Give a proper name to your column and insert after ":" the measure you created > Press "Enter"
  5. You will see the measure applied to all your rows of your table.
  6. After this just right click in any part of the table and choose "export all table"

 

Let's just make some attention points:

  • Not all measures works the same way for visuals and for tables.
  • Some adjusts may be need to your measures if they don't work as expected, but not that hard and you can always come back here for help 😃
  • This work with one table at time, so if you need to export more than one table, first create a join table (You can chose do this or in power query or with dax)
  • If you see, thet steps above are made by hand, and not automatic, but you can take some more steps within the Service (Power BI online - app.powerbi.com)
    • After you created you BI and input the measure columns, publish your bi.
    • In Service (app.powerbi.com) you can make an analysis on Excel,
    • just click in your report section
    • On the right side of the name of your report click in the [...].
    • Chose the option Analysis in Excel.
      • You will be prompted to install an add-on, and after you can open the file
    • Open the excel, and insert your power bi service credentials
    • now you get a pivot table with all the tables and measures created
      • Every time you update the pbi file  and publish, you can update the pivot table in this file
      • in excel its a lot easier to created macros for export your data 😃

 

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:

Sem título2.png

 

 

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.

View solution in original post

Super User
Super User

Re: Measure column export using R script

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)

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 348 members 3,332 guests
Please welcome our newest community members: