In this blog post, I want to take a closer look at the Enter data in Power BI and show you ways how to use it. Before, in most cases, we brought in data by connecting to a data source.
That won’t change of course, but Power BI also offers ways to manually create tables, one of which is using the Enter data option that you can find in the Data section of the Home tab.
There are three common use cases I want to discuss in this post. You can watch the full video of this tutorial at the bottom of this blog.
Create Tables For Measures
The first way you can use Enter data in Power BI is to create tables for measures. There are many different ways to organize a model. A common practice is to create one or more separate tables to store measures. You can use the Enter data option to create an empty table.
All we have to do is assign it a name. In this case, I’ll call our table Key Measures.
We can create a new measure by right-clicking the Key Measures table from the Fields pane.
Select a new measure, then hide Column1.
You’ll notice that the icon in front of the table name has changed, indicating it is now a dedicated measure table.
Create Supporting Tables
The second use case is for creating a small supporting table. If you want to add a table for dynamic grouping, you can use Enter data to create that supporting table on the fly. Let’s go back to the Home tab, click Enter data, paste in our table, give it a name, then press Enter.
Of course, there’s an important consideration or limitation to keep in mind when adding manually created tables to a model.
If at some point you need to modify or update the contents in that table, you can only do so from within the actual PBIX file. So if there’s any chance your supporting table might be subject to change, I’d recommend storing that in a separate file and connecting to that.
When a change is required, all you have to do is update that file and with the next refresh, your model is updated.
Create A Mock-up Model
The final use case is for creating a quick and small mock-up model for demo purposes or support queries because you can copy and paste the data directly into the Enter data window as long as it doesn’t exceed a 3000 cell limit. This is convenient because then you no longer have to keep or supply a separate source file along with your PBIX.
To do this, choose Enter data. We’re not going to select the header, but we’re going to leave it in the first cell and paste it into our values. It automatically suggested promoting headers. Now, if I want to undo that I can do so by clicking on Undo Headers. In this case, of course, it’s not needed.
I’ll call this table Sales and press Load.
Unlike tables created using DAX expressions, these tables will show up inside the query editor. So let’s create a DAX table as well. We’ll go to the Modelling ribbon and select a new table.
I’ll call this DAX Table.
Let’s go to the query editor and choose Transform data.
You can see that I have three tables here: our Key Measures table, our Pricing Segments table, and our Sales table, but we don’t have our DAX table.
Now, if you need to make modifications to any of the tables, you can do that from inside the query editor. Let’s say you wanted to add a sort column to our Pricing Segments table as an example.
We’ll select that query.
Then in the Applied Steps, press the gear icon.
This will open that Create Table window again, where we can modify the table contents, add new columns, or add rows. In this case, I’ll add a new column.
I’ll call this column Sort, enter a sort order, then press Done.
Then we’ll click on Close & Apply to save and get these loaded into our model.
***** Related Links *****
Power Query M Language Tutorial And Mastery
Power Query Features: An Overview
Power BI Unpivot Columns – Power Query Tutorial
Conclusion
To summarize, I’ve covered the three most common use cases for the Enter data option in Power BI. If you enjoyed the content covered in this particular tutorial, please don’t forget to subscribe to Enterprise DNA TV.
We have a huge amount of content coming out all the time from myself and a range of content creators, all of whom are dedicated to improving the way you use Power BI and the Power Platform.
Melissa
