Forum Discussion
selectcolumns and directquery
Hi, I work with a dataset in directquery mode, that I can't change and they don't want to change.
There is a table in the datamodel not in a relationship to the tables that ARE in a relationship, , that I needed to connect to, but the data model wouldn't let me connect to that table. So using the code SELECTCOLUMNS, I made a duplicate of the table. and Connected to my datamodel , and everything worked great, using that copy of the table.
I learned that when using the SELECTCOLUMNS function, it prevents the datamodel from being update and as a result, I have a working report, that isn't updating the dataset.
I used selectcolumns because in creating a duplicate table manually, some of the joins weren't working perfectly, and I had to manually drop the data into the table. So my question is, is there anyway around that, or is there anyway i can manually create an exact duplicate of that table and pump data from the orginal into the new table? It's a lookup table.
7 Replies
Hi!
What do you mean by "in creating a duplicate table manually"?
Why not just reference or copy the table in Transform Data / power query?Hi Gabriel. I was able to make a copy of the table and do everything I needed to do with it.
However I'll have to manually update it, where when I created a table using SELECTCOLUMNS, I didn't have to.
So where i am now, DirectQuery updates are working, but because it's no longer a table created by SELECTCOLUMNS, I have to update the table manually.Hello Steven, I was facing the same issue with you. I was trying to append three tables together using selectcolumns and union together. The problem is two of my tables are coming from DirectQuery and the other one is coming from another dataflow. So I was able to create this virtual table using UNION(SELECTCOLUMNS...) but when I publish the dataset I cannot complete refresh it gives this error :" Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery". My position is similiar to yours, I am not the owner of the data source or model neither. Do you know how to overcome this issue? Is it possible to feed the newly created table from three different tables automaticaly ? Thank you.
Hi Gabriel. The problem is that I'm working with a dataset where I'm not the owner and don't have permissions to change it. As a result, I don't have the ability to copy or reference any of the tables in the dataset. Those menu options are unavailable to me.
But I've created a few local tables, and THOSE I can copy or reference.
I hope that helps?Hi Anonymous,
Maybe you can try this code to copy the directqueryTable.
Table = FILTER('directqueryTable',TRUE())However, the table will not update until you refresh the data manually. Normally, you cannot join to this table, which I understand to mean that you have no way to create other relationships for this table because other relationships already exist. Please let me know if I'm misunderstanding.Or you can add a new connection to this table in power query.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 🙂
