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.
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.
Leave a Reply