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.

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

 

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

Leave a Reply

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