selectcolumns and directquery

Just came across this while trying to figure out something else and thought I would add a little to the conversation.

What I understood from the above is that there is a Source A which the original poster (OP) controls and Source B a semantic model which OP doesn't control. Therefore, OP can bring in tables from Source A and transform them in PowerQuery, however, tables brought in from Source B using DirectQuery cannot be further transformed in PowerQuery.

It is possible to bring in tables from Source B using Import mode which then allows you to do all the usual transformations using PowerQuery BUT then means data freshness is not automatic.

First, go to Semantic Model settings, then Server settings and copy the connection string. (If you're not the owner, you need the owner to provide this.) You need the bit between 'Data Source=' and ';Initial Catalog='. Usually it's in the form powerbi://api.powerbi.com/v1.0/myorg/workspace_name .

Second, open your pbix file and go to Power Query Editor. Click New Source, then Analysis Services.

Thirdly, your powerbi://api.powerbi.com/v1.0/myorg/workspace_name string from Step 1 into the 'server' box and click 'OK'.

This opens up a Navigator pane simiar to the one for dataflows but the directories are semantic models. Click through and select the table you are interested in. Though beware that clicking on multiple tables will result in PowerQuery trying to bring the selected tables into one table, so you're better off bringing them in individually.

I hope this helps someone out 🙂