selectcolumns and directquery

[Anonymous]

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.

[Anonymous]

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?

[v-chenwuz-msft]

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.

[Gabriel_Walkman]

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?

[Anonymous]

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.

[tolunayyilmaz]

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.

[Anonymous]

HI tolunayyilmaz.  You definitely got the same error I was getting, and it sounds like the same challenge I had.  Another group controlled and maintained many of our Datasets that we use.  Without the ability to modify them for my own use, I could only read them, add custom columns, and relate them to the tables that I've created externally.

I tried various dax functions, which all worked as long as i was on the desktop report locally! I was also able to initially publish and see the report and it's data at powerbi.com.  But in reality, the functions simply weren't doing (I tried to use TREATAS often) what they were supposed to be doing online. And on powerbi.com the refresh failed and errors appeared.

I did learn that trying to create any function who's goal was to connect to the tables in the direct query, failed, on PowerBi (I used LOOKUPVALUE, TREATAS, etc.).  Frustrating, huh!

I tried everything I could think of without success until the following solution.
* Created excel workbooks seperately using the same datasets I was unable to modify
* Then saved the workbooks and uploaded them to our SP site
* Set them up to refresh daily.
* Then linked them to my PowerBI reports.

Problem solved.  each table acted as if they were independent to the datasets I used, and they worked perfectly.  Of course, more maintenance is required to make sure the excel workbooks are stable, but it was a solution.  

Of course not everyone has an SP site to do this or the access to do it.
If you can though, this is an excellent workaround.  
But if you don't, you could also copy the excel files to a shared onedrive folder and link your reports to them.

I'm willing to bet there are other workarounds but based on my level of knowledge and other factors, this was my solution and ok'd by management.

I was unable to find any help on this issue that directly addressed the issues I faced. But maybe you might, and if you do, please let me know what YOUR solution is.

Hope my idea helps.