It can be a lot faster to enter data directly into a Power BI table. At times it is not practical to directly edit a data source or you may need to create a quick index table. We explain the methods available for entering and editing data in a Power BI table.
To enter data directly into a Microsoft Power BI dashboard or report, press the Enter Data button at the top of Power BI Desktop. The Create Table screen appears and allows you to manually enter data, or paste data that was copied from a spreadsheet or other structured data source.
Create a Table in Power BI without having to Get Data and deal with an additional data source in your data model.
Let’s jump in!
How to Enter Data Into a Power BI Table
By creating a new table in Power BI, you can speed up report development, and minimize the number of data sources that have to be configured at a later time. The data that is entered into a table is saved directly into the Power BI .pbix file. This has the advantage of making it easier to share data with other members of your business, and the data will be easy to manipulate in the future. One drawback is that the data entry method is fairly primitive and works similarly to typing into a .csv file.
While keeping these limitations in mind, here is how to enter data into a Power BI table.
Click on the Home Tab from the Power BI Ribbon
Click the Enter Data button
After pressing Enter Data the Create Table Screen will appear
In the Create Table screen, you can manually enter data and navigate around it like a csv file. The create table screen is very limited. You will not be able to add any data formatting or formulas like you would in an Excel sheet. If you need to do anything that is more complicated, it will likely be easier to use the Get Data feature in Power BI and import a new Excel Sheet.
Another popular option is to create the data in an Excel sheet and copy and paste it into the Create Table screen.
Note: You can navigate around the Create Table screen like you would an Excel sheet. Press Tab to move between columns, and press Return to go down a row. You can even navigate using the arrow keys on your keyboard .
If you add too many columns or rows and need to delete some of them, you can right-click on the rows and columns and select delete.
Copy and Pasting Data Into a New Power BI Table
When you copy and paste data into the Create Table screen there are a few considerations that you should be aware of.
- If you click on the top row it will place the first row of the information you copied and pasted as headers.
- There is a limit to how much data you can copy and paste. If you run into an error or Power BI crashes, you will want to import the data using Get Data
- If you copy Excel formulas, they will paste as values
After you give the new table a name, you can click the Load button and the table will be made available to combine and blend in with the other data in your dataset.
The new table that was created will show up in the Data Column. You are able to work with it for data analysis and modeling like you would any other table or data source within your report.
How to Edit a New Table in Power BI
Once you have created a new table in Power BI it can be a bit confusing how to actually edit it. While there is an Enter Data button on the Power BI ribbon, there is no edit button.
To edit a newly created table in Power BI, open the Power Query Editor and Change the Source. The Change Source settings button brings up the Create Table screen where you can modify the data. Access the Power Query Editor by right-clicking the new table in the data panel and selecting Edit Query.
Right click the name of the new table, and select Edit Query to launch the Power Query Editor
Change Source under Applied Steps and select the cog wheel to the right of Source to launch the Create Table screen.
Note: Power BI will automatically attempt to detect the data type of the data being entered into a new table. The second Changed Type step of Power Query is automatically generated. You can make changes to the data types using the Power Query editor like you would with any other data type. You can also add relationships or join it against another table.
Additional Considerations and Potential Issues When Entering Data Into Power Bi Table
Accurate data entry is essential for getting the most out of Power BI Table. Incorrect data entry can lead to errors in data analysis and lead to incorrect results. To ensure that your data entry is accurate, there are some common issues to watch out for.
- Duplicate Records – Duplicate records can easily be entered into the Power BI Table. Duplicate records can be removed by right-clicking on the column header in the Power Query editor and selecting Remove Duplicates.
- Incorrect Data Types – When entering data into the Power BI Table, it’s important to make sure the data types are correct. Incorrect data types can cause errors in your data analysis.
- Formatting Issues – It’s a recommended best practice to enter numbers without punctuation like commas and dollar signs. It is easier to transform the data and adjust formatting after creating a calculated measure than it is to type it in.
- Missing Data – Missing data can easily be entered into the Power BI Table especially when entering it manually. It may be prudent to add a matrix visual and calculating subtotals to ensure that it ties out. Nothing is worse than bad data analysis because of a typo!
- Performance – One advantage of entering data directly into Power BI is performance. While Power BI can read a csv faster than an .xlsx file, it reads data that’s been entered in directly even faster. That being said, this method is still not recommend for entering in large volumes of data.
By paying attention to these common issues when entering data into the Power BI Table, you can ensure accurate data entry and avoid any errors in your data analysis.
Because there are so many opportunities for errors when typing data directly into Power BI, we generally don’t recommend it unless it is for a very small quick index.
We recommend entering data into Microsoft Excel and copying and pasting it into Power BI afterward. It will be easier to subtotal or to apply calculations and formulas.
How to Input Data From User Into Power BI
Now that you know how to enter data into a Power BI Table, the next step in mastering Power BI is allowing users to directly enter data into a dashboard.
This problem comes up in a lot of enterprise and business deployments of Power BI.
Aside from adding commentary to reports, Power BI is mostly a business intelligence tool that’s used to create data visualizations or perform data analysis. It is not designed with a core functionality of users inputting data into it, and writing that data back to the data source.
Comments are highly limited in use cases because they’re difficult to export or version off for a future audit trail.
Luckily, Microsoft has a low-code app solution called PowerApps that compliments Power BI.
PowerApps is a separate product from Power BI.
It lets business users create simple to complex forms that can be embedded directly into a Power BI dashboard without needing to know any programming.
The integration is easy to set up once the app is created.
You can add a Power Apps visual and it becomes embedded into the report, like you would add a visual.
The Guys in a Cube do a great job of explaining PowerApps and Power BI integration!
Note: There is some nuance with using PowerApps as the licensing varies based on the type of dataset that you are working with. However, most enterprise editions of Microsoft 365 include PowerApps licenses when connecting to Excel or a CSV file.
Try embedding a PowerApp into a Power BI report and connect both systems to an Excel file saved on OneDrive or SharePoint.
Users will be able to type data into a PowerApp and after the two systems have a chance to sync, the dataset can be refreshed to pull in the updated data.
Entering data directly int Power BI can be a big time saver. It’s best suited for small tables and quick indexes. For more extensive datasets we recommend creating a dataset in Microsoft Excel and copying and pasting it into Power BI to avoid the potential for data entry errors.