How to do advanced data manipulation in Power Bi

[gregboothdelt]

We were hoping to pull some data into a data warehouse staging area- so we could do a lot of manipulation of the data. The resulting data would be made available for querying in power bi.

However, for the main data source we can only import this data into power bi (we cannot connect an ETL tool to it).

So all the maniuplation/data prep will need to be done in power bi - but i am not sure if this will be possible.

For example , for each record, there is some coordinates. We need to martch this to the nearest house. We were going to do this as part of the ETL build phase using T-SQL. 

Is it realistc that we even think about doing this in Power Bi itself (for example using power query).

[lbendlin · Accepted solution]

Depends.  Power Query is a very powerful language, and it can do a lot of nifty ETL things. But it's not comparable with SSIS or Informatica etc. You will run into performance issues when dealing with millions of rows and even less once you start doing lots of merges and lookups etc.  (There are optimization options for these but they require lots and lots of memory to be successful). 

My approach usually is to apply only the absolutely necessary transforms in Power Query (avoiding merges/joins wherever possible) and then let the Vertipaq engine and the data model do the dirty work.  That will be much faster as it's all in memory already.

Can you do a proof of concept?