Power BI Power Query

Easy way how to make Power BI table from scratch, without an external data source

There are two options:
– Power Query function #table
– Enter Data feature in Power BI

#table function, in my opinion, is a more flexible way if you like to work with M code, but sometimes time-consuming. Here is a reusable solution to quickly build Power Query #table function in Excel with text concatenation.

As you see in the result #table contains column headers and properly arranged column values.


//column headers
{"Country code","Country Name","Currency"},

//row content

{"USA","United States of America","USD"},

In the same time, you have to repeatedly use certain symbols to make it understandable for Power Query. Differences come only at the beginning of the table and at the end.

With that in mind, you can use concatenation symbol & in Excel end build flexible solution to generate #table function. You can download this workbook here.

The result in Excel that you can copy in Power Query Advanced Editor looks like this.

Which gives a good looking table in Power Query.

With Power BI Power Query Enter Data it is a bit easier.
Copy your table in Excel (Ctrl+C), click on Enter Data, paste it (Ctrl+V) and OK.

But after that done, you can not edit content in M code like in previous option with #table function. You can make corrections when you go to Edit Queries -> select the corresponding query -> find Applied Steps section and click on the gear icon next to Source.


The manually created table appears, and you can make changes.


2 comments on “Easy way how to make Power BI table from scratch, without an external data source

  1. Pieter Bollen

    Nice post and clear example !
    With the new TEXTJOIN function in Excel this example with data from Excel can be simplified to
    =”#table({“&TEXTJOIN(“””,”””;TRUE;A1:C1)& “},{”
    for the title, and
    =”””{“&TEXTJOIN(“””,”””;TRUE;A2:C2)& “””},”
    for the data, and to finish
    & “””}})”

Leave a Reply

Your email address will not be published. Required fields are marked *