Measure column export using R script

[Kolumam]

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.

[TomMartens]

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

[Anonymous]

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]

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?

[TomMartens]

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

[Kolumam]

Hi @TomMartens @Anonymous 

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

Regards

Krishna

[Anonymous · Accepted 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!

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:

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.

[ImkeF]

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)