cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker
Skilled Sharer
Skilled Sharer

Extract data from PowerBI

Hi everyone,

 

I have a table of data which in Power BI i can get to do what I want however I need to extract this to excel for someone.

 

The data is made up of consignment numbers, wagon numbers, dates, and net tonnes. Each consignment number will be repeated for the number of wagons within that consignment (generally this is 16 times but can vary). The picture below shows what I am trying to do. I need want a table which shows just the unique consignment number with the date loaded and the sum of the net tonnes in that consignment (i.e. if there were 16 wagons with 20 ton in each of them then the train net tonnes for that consignment would be 320 tonnes).

 

Hope you can help with this.

 

Giles

 

Power BI data.PNG

1 ACCEPTED SOLUTION
GilesWalker
Skilled Sharer
Skilled Sharer

Someone in IT managed to assist with this. The answer was to create a table and then enter the following formula:

 

Train Report net Sum = SUMMARIZE('Train report master data', 'Train report master data'[Consignment], "Net Total", SUM('Train report master data'[Net]))

View solution in original post

8 REPLIES 8
brendanbannon
Frequent Visitor

Hi!

 

I found another way to extract / copy data from within Power BI for use in another application such as Excel.

 

  • Within Power BI, navigate to the DATA view (Table Icon in top left corner)
  • Right-click on the table you want to extract from the FIELDS panel on the right side of the page
  • Select "Copy Table" to copy all data to the clipboard
  • Paste the copied data into your desired location

Note: I'm not sure if there is a size limit.  This worked for me with ~20,000 rows and ~25 columns.

 

Cheers!

brendanbannon
Frequent Visitor

Hi!

 

I found another way to extract data from Power BI.

 

You can navigate to the DATA tab (Table/Matrix Icon), right-click on the desired table to extract, and select "Copy Table" to copy the entire table to your clipboard.  You can then go to Excel or another application and paste the data.  

 

NOTE: I'm not sure if there is a size limit.  I copied ~20,000 rows and ~25 columns without issues.

ImkeF
Super User II
Super User II

With the December update you can use the workaround via R to export your data from Power BI to txt or SQL-server: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/

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

@ImkeF - thanks for the tip, and also I have saved your website in my favorites, like what you are doing please keep it going.

GilesWalker
Skilled Sharer
Skilled Sharer

Someone in IT managed to assist with this. The answer was to create a table and then enter the following formula:

 

Train Report net Sum = SUMMARIZE('Train report master data', 'Train report master data'[Consignment], "Net Total", SUM('Train report master data'[Net]))

View solution in original post

Hi Giles, 

 

Could you explain how you resolved this a bit more? Sharing some of our PowerBI insights has been an issue due to the lack of an 'export' feature.

 

Where did you input that formula and how did it export to excel for you?

 

Thanks!

@cwayne758 not a problem, will do my best to explain what i did.

 

The only way to extract the data from Power BI is to copy an entire table in the fields tab. However some tables can be very large and therefore cannot be copied over easily. Within Power BI there is an option to create a formulated table within the Data screen in the desktop version (see picture)

 

Power bi data table.PNG

 

You will then see the along the ribbon an option called New Table. The new table feature creates a table based off of DAX formula. This is where the formula I posted before comes in.

 

Train Report net Sum = SUMMARIZE('Train report master data', 'Train report master data'[Consignment], "Net Total", SUM('Train report master data'[Net]))

 

I called the table Train Report net Sum. SUMMARIZE creates a filtered table with just the DISTINCT consignmnet numbers, the "net total" is the new column I want, then the SUM('Train report master data'[Net] gives me the sum of the Net weight to the distinct count of consignment numbers.

 

Its only a small table as this is all the data I needed, someone else may now how to do a better one though.

 

Once this table was created I then right click on it in the fields tab and click copy table. Then this can be pasted into excel.

 

I hope this was clear, if not please let me know.

 

Thanks,

 

Giles

HUGE!! thank you very much for breaking it down.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors