Skip to main content

How to type or cut & paste data directly into PowerQuery / PowerBI

Most of the time data will need to be imported into the data model from a source so that the connection is 'live' and can be updated by refreshing the source data. It is also possible to enter data straight into the data model through the 'Enter Data' function. This function allows data to be typed or cut & pasted into a table.

This way of entering data can be used to hard code any lookup tables that might currently be Excel files. If the data is hard coded in the model using the process below then the risk of links to files and filenames is mitigated.

Here are the steps required to enter data directly into PowerQuery

  • In the ribbon under 'Home' click the 'Enter Data' button to display the 'Create Table' window

  • Enter the data either by typing it in or cut and paste from another source. Be mindful of the header row to make sure if you have headers, the headers are pasted into the header row.

  • Columns can be inserted or deleted by right clicking on the title row

  • Once completed, give the table a meaningful name by entering a title in the 'Name' box and then click 'OK'.
  • You'll now see the table listed in the queries pane of the PowerQuery window. In the table preview window will be the data you previously entered

  • The query will also have a single step associated with it in the Query Settings pane (on the right) titled 'Source'.


Top tip!! 

If you wish to edit the data previously entered, double click on the 'Source' step to re-open the 'Create Table' window where you can amend the existing data or add additional rows, columns or cells.

  • Notice that the data types default to Text format even though the values entered are clearly numbers. If you try and apply any functions to these columns they'll be treated as text and could return an error if the expectation that they're numbers
    • To change the formatting, click on the 'abc' icon in the title bar and change the formatting to the required type or select both columns and then in the ribbon under 'Transform' select 'Detect Data Type'
    • Once the format is changed on each column, the following changes can be observed
      • the icon next to the columns changes from 'abc' to '123', 
      • the contents of the cells in the preview window change to Italic formatting 
      • Cell formatting also changes from left justified to right justified
      • An additional step is listed in the 'applied steps' pane and the formula associated with the step can be seen in the formula bar (Table.TransformColumnTypes with the associated arguments, 
      • https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumntypes

The M language code that has been created by PowerQuery can be seen by clicking the 'advanced editor' button in the ribbon

Here you can see the code generated to replicate the table is made up of a number of M functions. Fortunately, there is no need to understand this code as the visual interface takes care of all the coding.


Labels:

Comments

Post a Comment

Popular posts from this blog

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...
5 comments
Read more