Forum Discussion
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
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]))
8 Replies
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/
ImkeF - thanks for the tip, and also I have saved your website in my favorites, like what you are doing please keep it going.
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]))
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)
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
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.
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!
