📄Entering Data without Data Source using Power Query M language

When it comes to enter data manually in Power BI, the most obvious choise is the enter data button in data pane on home tab. However, entering data through this way rquires typing, and the data entered can not be altered at a later stage easily because it is stored in binary format.

Enter Data Window

If you go to the query editor, this is the code behind the above table.



As you can see, the table is stored in binary format, and you cannot edit this data easily. In addition, you can not enter randomely or computer generated data through create table wizard. You can only enter few values through typing.

Other Methods

Today, we will show you some other methods through which you can enter data in Power BI, without selecting an external data source. These methods invlove the use of Power Query Editor. Power Query uses the M language to create and edit queries. The most import thing to keep in mind about M language in Power BI is that it is case sensitive. Meaning ‘A’ and ‘a’ are two saperate things. You can use the Blank Query from Get Data menu to create lists and tables dynamically in Power Query using M language.



Comma Separated List
The firs method is to add simple lists. Add comma separated numbers or data inside curly brackets, and the same will be added as a list to your Power BI data pane. Non numeric data should be inside inverted commas.



List Function
You can also use the “List” function to add lists more quickly. For example, you can add a list of numbers starting with one and counting up to ten, using the function “List.Numbers(1,10)”. If you want to create a list of same numbers with increment of two, you can use the function “= List.Numbers(1,10,2)”, and the result will be {1,3,5,7,9,11,13,15,17,19}.



Table Function
However, it is recommended that you use Table instead of List, to enter data into Power Query. You can use List function, inside a Table function, to convert a list into a table.



In the example above, a list with comma saperated data has been entered into a Table function, and then data is split into columns using comma delimiter. Now, you can simple “Use First Row as Headers” as well.

Table.FromRows Function
You can also use the function “Table.FromRows”, where each list is considered a row. The first row can be used as headers.



Table.FromColumns Function
Or you can enter data using “Table.FromColumns”, which in my openion is the best way to enter large amount of randomly generated data. For example, instead of fixed values, we can use functions to generate lists of random values, which will serve as columns, and we can define headers. We often generate random data in large quantities to demonstrate various scenarios or test machine learning models. This function is perfect to perform such tasks.



In the above function, we use Table.FromColumns function to enter two lists as two columns x and y, both lists have one thousand rows with randomly generated numbers between ten to twenty.