Forum Discussion
selectcolumns and directquery
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 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.