Use Power BI's Enter Data Feature In Excel

EDIT: This article is no longer necessary as Excel’s version of Power Query now has an Enter Data feature built in. But the article below will show you how the Enter Data feature works, so still has some value. You do need Excel 2022 or Office 365. If you have an earlier version of Excel, you may still need these steps.

One of the cool things about Power BI is you have a nice “Enter Data” button on the home ribbon where you can quickly add data to your model. The table isn’t dynamic, but sometimes you just need a small table to finish your model to add descriptions or some other bit of data without creating another table in a source that needs to be connected to. Enter Data is perfect for this.

It looks like the image above. You just start typing the column names, some data, and name your table. The merge it with other queries or bring it directly into the data model.

Except that isn’t Power BI. I created that image in Excel! Notice how it is green instead of Power BI’s yellow? How did I do that? With just a little bit of trickery.

First, I created a table using Enter Data in Power BI with just a single column and record. Then I copied the M Code Power BI created. It looks like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

Now create a new Blank query in Excel’s Power Query, and copy the Power BI code, then paste it in the Advanced Editor. Now in that query, select the little gear icon next to the Source statement, just like you would in Power BI to edit the table.

That’s it. The Create Table dialog shows up and you can add and change data and records however you like. I keep that code snippet in Evernote where I can do this in Excel anytime I need to without firing up Power BI to get the code. Power Query is just storing this data as a compressed binary string of characters, and can usually store up to 3,000 cells.

Why would you do this though? Unlike Power BI, Excel has a place for this kind of data, in a Table in the worksheet you import into Power Query. That is probably why the Excel team didn’t bother to expose this functionality anywhere in Excel. The underlying Power Query tool is very similar to the one in Power BI though, so all of that Create Table code is still there.

Maybe you have some data you want to be hidden, or at least difficult to find or modify, and don’t want to fool with hiding sheets, locking workbook properties, or setting worksheet’s hidden property in VBA to xlVeryHidden. All of those work well, but it also makes it harder for you to get at to edit if you need to in the future. This method is easy because it stays in the Power Query UI, where you are comfortable working.

Avoid Using Excel XLS Files As A Data Source

Power Query in both Power BI and Excel can use all Excel file formats as a data source, including the old 2003 format that ends in XLS. Most of the time, this works fine, but I recently hit an issue that consistently returned incorrect results.

Microsoft replaced the XLS format over a decade ago when Office 2007 launched with the new .XLSX format, but there are still companies that use the older XLS format. I frequently get data exported from a national payroll service that offers the XLS file format as the only spreadsheet based format.

It turns out that if data is formatted a certain way in the XLS file, Power Query will return the wrong value. Look at the data below.

Column A has integers 1-4, and then a negative 5. Column B is simply a list of formulas pointing to column A, but it is formatted with the number format 0.00;[Red]0.00 which is a normal format in the Number section. It just shows the number in red with no negative sign. I’m not sure why anyone would format a number this way. A negative number needs to either have a negative sign or be in parenthesis, but I’ve seen a lot of people use this, and this national payroll provider’s systems are programmed to use for all numerical data.

If the file is a 2007 or later format, this is no problem. But oddly enough, Power Query doesn’t see it that way in the XLS format. Power Query sees the below data when you connect to the file. Here is what it looks like when you start the import process:

In the preview window

In the editor

You can see this is not just a visual issue. I added a column called “Addition” that just adds the values in [Regular Number] to [Special Format]. The positive numbers 1-4 added correctly, but -5 was added to 5 (instead of -5) returning the incorrect result of 0. It should be -10.

There is no fix for this. I’ve contacted Microsoft’s Power BI team and they confirmed it but understandably weren’t going to commit to spending a lot effort to fix a file format that was discontinued years before Power BI was first released.

Those of you that are a bit more savvy in working with XLS files might be thinking “Well, of course. To fully support XLS files you need to have the 2010 MIcrosoft Access Data Engine files installed.” If you’ve ever worked with Microsoft Access files in Power BI you have these files. And yes, the ACE engine does have some addition support for XLS beyond what Power Query supports natively, but it does not fix this issue. There are at least two workarounds for this:

1. Save the file as an XLSX file or move the tab with the data you need into an existing XLSX file.

2. Change the format, or better yet, remove all formatting. As long as the negative sign is visible to the human eye, then Power Query will see it as well.

It took me about an hour to figure out why a report I recently wrote was returning bad data. As you can imagine, the number format was the last thing I would consider to be an issue. I don’t know if this is the only issue with XLS files, but going forward, I am putting filters into my queries that will filter out XLS files before any File/Combine operations are done with Excel files, and fully documenting processes to save all files as XLSX before storing them in network, SharePoint, or Azure DataLake folders.

If you have XLS files in your workflow, you should consider doing the same. You never know what data formatting landmine lurks in that ancient file format that Power Query will read incorrectly.