Copy and Paste Data from and into Power BI

We excel folks, have this inherent need (every once in a while) to copy data from Power BI into excel or vice-versa (copy data from Excel to Power BI). To settle your copy and paste cravings, in this post I am going to share with you 3 simple tricks to

1. Copy and Paste Data from Excel to Power BI
2. Copy and Paste Data from Power BI to Excel
3. Copy and Paste Data from a Power BI Visual to Excel

Let’s begin..

 

#1 – Copy and Paste Data from Excel to Power BI

If you are an avid user of Power BI, why would you even consider copy-pasting the data from Excel (or any other source) into Power BI especially when you have GET DATA (using Power Query)?

Simple Answer : It’s easier and faster, at times

Imagine you need to create a small disconnected table with just 2 columns and 3 rows. It’s a lot easier if you could just create this little table in excel and somehow paste in Power BI. Here is how you can do it..

1. Create the data in Excel and Copy it – Ctrl + C
2. In Power BI window >> Home Tab >> Enter Data
3. As soon as the Create Table Box opens up, paste the data Ctrl + V. Additionally if you want you can also give you table a name
4. Click on Load. Done! Its that simple

Now there are definitely some pros and cons of using this method

Pros : It’s quick and fast

Cons :

1. Once you click on load, making changes to the data are only possible in Power Query (in the source step)
2. Its not dynamic (obvious reasons.. its just a copy and paste)
3. You can’t copy-paste large amount of data in this way.
   • And how much is large data?
   • Power BI decides that and will give you a notification if it finds it to be large enough to be copy and pasted.
   • In that case you’ll have to load the data using the GET DATA Option of Power BI

 

#2 – Copy and Paste Data from Power BI to Excel

Now sometimes you would also need to take the data from Power BI to Excel. When I say data I mean the tables that have been loaded in the data model. Here is how you can copy and paste the data from Power BI to Excel

1. In the data tab
2. Right click anywhere on the table (data)
3. You’ll see two options Copy and Copy Table
   • Copy Option – This will copy the only the column selected (in yellow and grey)
   • Copy Table Options – This will copy the data for the entire table
4. You can then simply paste the data into excel

Note that : In case you have applied filters on the table in Power BI, the copy or copy table options will only copy filtered data

 

#3 – Copy and Paste Data from a Power BI Visual to Excel

The third common requirement is to copy and paste the underlying data from the visual / table or a matrix in Power BI. Frankly speaking, Power BI hasn’t got very friendly options to Copy and Paste Data from a Power BI Visual to Excel. But whatever there is, let’s take a look

The first option is to Export Data – This option is mostly present for any visual of Power BI

1. Click on the 3 Dots (ellipsis) Symbol
2. Click Export
3. You can now export the data (in tabular form) of the visual in a CSV file. Unfortunately a separate CSV gets created for each visual

The second option is to manually select, copy and then paste the data –

1. Hold the Ctrl Key and select the columns you want to copy
2. Right Click and choose Copy >> Copy Selection
3. Paste (Ctrl + V) in Excel

Pros – At-least you have a way to copy the data from the Power BI Visual

Cons – When you paste the data, it can be unformatted

1. The dates can be wrongly formatted
2. Even a complex Pivot / Matrix will not be appropriately pasted

 

In my option Power BI has still some work to do in terms of providing robust options for copying and pasting data, especially from Power BI visuals into Excel. But I also guess these bottlenecks are built in on Purpose because Microsoft wants people to move to Power BI pro / premium platform rather than using to do the data heavy lifting and then pasting the results in Excel.

1. What do you think about it ?
2. Where have you felt a dire need to copy data from Power BI to Excel or vice-versa and what was your workaround for that?

 

Interested in the Video Format ?

 

Other Interesting Power BI Tutorials

1. Calculating Fiscal Year in Power BI
2. How relationships work in Power BI
3. Creating an “Excel like” Pivot in Power BI
4. Top Selling Product Analysis