Since it was a small list of pages and the list was static, I decided to use the Enter Data feature. In this post, we’re going to look at how to copy and paste data from an Excel workbook or text file into Power BI using the Enter Data feature.
Video Tutorial
Why Use The Enter Data Feature?
This feature would usually only be used for a supplementary data table. For example, maybe Power BI connects to your sales data from some database. All the data is in the database, except for details about regions you want to report for. A supplementary table that shows which states are in which sales region is needed to create this regional reporting. This list isn’t going to change because there aren’t new states being added in the US and sales region reporting remains the same. In this case, we could use the Enter Data feature. As a guide, if all three of these are true then you might consider using the Enter Data feature:
The data set is small.The data is static and won’t be changing frequently (ideally it never changes).You don’t want to store the data set in a separate location like a database, Excel workbook or text file.
Otherwise, you should stick to keeping the data elsewhere and connecting to it through the usual Power BI data connectors.
Entering Data In Power BI
If you decide to use the Enter Data feature, this can be accessed in Power BI by going to the Home tab and using the Enter Data command.
This will open up the Create Table dialog box. Here is where you will be able to manually type in data or copy and paste it from Excel or some other file. You can change the default column heading from Column1 by double clicking on it, giving it a new name and pressing Enter. If you copy and paste the data from another source like Excel, Power BI will automatically promote the first entry to the column headers, thereby saving you time renaming all the columns. If the first entry wasn’t meant to be a column header, you can easily Undo Headers. You can also rename the table here before loading it into Power BI. After your data is entered, there are two option. You can Edit it further inside the power query editor if it’s not quite in the right format for use, or you can Load the data as is into Power BI.
Adding Or Editing Entered Data
After loading the data, you will be able to see it in the data view as usual like any data imported into Power BI. Even though this feature is meant for data that is static, there will inevitably come a time when you need to add more rows or edit existing rows in your created tables. It’s not entirely obvious where you can do this.
Even though you are entering data, it is in fact a query you are creating and it can be edited like any other query. Go to the Home tab and press the Edit Query command.
Inside the query editor, you might be wondering about the Enter Data command in the Home tab. But this does not allow you to edit existing tables, only to create new tables. You will need to select your table and click on the settings for the Source step. This will allow you to add or edit data in an existing table. This opens up the familiar Create Table dialog box where you can enter new data or edit edit existing data. Once you’re done making any changes, you can Close & Apply the changes to save your data.
Conclusions
While this is not a feature you will be using all the time, the need may come up from now and then. Just make sure you are using it for the right reasons and not creating something that’s hard to maintain.
