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.

#table(

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

//row content

{
{"LVA","Latvia","EUR"},
{"DEU","Germany","EUR"},
{"FRA","France","EUR"},
{"USA","United States of America","USD"},
{"URY","Uruguay","UYU"}
}
       )

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.
01_table_function_concatenation_in_Excel_spreadsheet

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

Which gives a good looking table in Power Query.
03_power_query_table_result

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.
04_paste_in_enter_data_feature_power_bi

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.

05_applied_steps

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

 


Posted

in

,

Comments

One response to “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 *