Hi Everyone, Can PBI support to run a query which appends two different data source queries now? One of data source is from VSTS and the other is from SQL Server. I append these 2 queries as a new query.It works well when I refresh on PowerBI Desktop, but it refreshs failed on PowerBI Service and notes me as below. "The provider is not support or due to invalid data source. Please check the data source connection string." Thank you in advence! Regards,Kelly

Anonymous AnonymousHi, did you get your report to work at Power BI service - that is - get the automatic refresh working?I have a similiar problem: I have a PBI workbook with SQL Server and documentDB data and I have appended a new query from these two data sources. The automatic refresh was not completed and gives me an error indicating that the Appended Query table was the root of the problem (CRM_ERP_total_sales).Any idea how to fix this? I am combining old sales data from SQL Server and combining it with the new sales data from Azure DocumentDB. edit: I am using for this case the new Gateway in Personal mode and I have defined the data sources in Edit Credentials. I had to install this GW in Personal mode just for this report, because our existing On-premises GW did not function with both on-premises SQL server data and cloud data (Azure DocumentDB). edit2: I could swear this worked yesterday when I tried to refresh the data in Power BI service On demand (it refreshed the data, there's 8 second difference what the dashboard tile refresh time shows and Refresh History), but now when I look the refresh history it says all of them failed, for the same reason

Anonymous @Simon_Hou-MSFT Hi again Ross and thanks for the fast reply, The root cause why I am using Personal GW instead of On-premises GW is because I cannot use On-premises GW when I am using multiple data sources - in this case online source Azure DocumentDB and on-premises SQL Server data. I made a thread about this problem and mod Simon_Hou-MSFT gave me a tip to use Personal GW. In essence, this whole ordeal is about making a neat Power BI report using our old sales data from a local SQL Server and new sales data from Azure DocumentDB. I am pulling these two data sources into a single report, aligning the columns and appending those two queries as a New Query ---> One query with old & New sales data --> clear 2017 sales data to report. We are launching our new ERP with DocumentDB database soon. https://community.powerbi.com/t5/Service/Making-a-auto-refresh-Power-BI-report-using-Azure-DocumentDB-and/m-p/295985/highlight/true#M34583 I couldn't image that combining Azure DocumentDB and local SQL Server data would be so complex. Please, please dear Microsoft gurus, help me solve this problem! Again, when I refresh the report in Power BI Desktop if works fine, but I cannot add the data sources and make the report refreshed automatically at Power BI Service when I tried to use On-premises GW. I can add the data sources when using the Personal GW, but it gives me the error below. I don't know if this is related to the problem, but the Azure DocumentDB datasource loads quite slowly (my report with max a couple ten thousands rows and just tens of columns take 10-20 mins to refresh) in Desktop, but it still refreshes the data. It was quicker before, like 4-5 weeks ago, but something might have happened with azure cookies or something. PS: Making a support ticket is useless. I've made two of them. It goes to MS out-sourced tech support in India (Mindtree co.) . They paste web links (which I've already read) and keep me playing this email ping-pong game which does not end anywhere. They give me useless, vague advice like (direct quote):"Solution 1Get data from cosmos using connector without ODBCGet data from on premise source (any data)Create the report and publish itSetup the gateway and refresh "I know how the game goes as a general process, but I now have problem with this detail and they cannot provide me with a solution.

you can use UNION in DAX instead of appending/merging it in power query. it worked for me. table_final = UNION(SELECTCOLUMNS(table_1,"col1",table_1[col1],"col2",table_1[col2]),SELECTCOLUMNS(table_2,"col1",table_2[col1],"col2",table_2[col2]))

When you say you can use UNION in DAX, do you mean on the front end visualization in PBI (not in Power Quary)? Sorry I've experimented with it in visualizations but am by no means an expert. The reason I'm asking is I have a Common Data Service (v2) table (coming from xxxx.crm.dynamics.com and an onsite Analysis Services cube. I merge data from the CDS entity (table) to data imported from the cube, do a bunch of calcs and then use the resulting table(s) for even more calcs all in Power Query. Everything refreshes fine on my desktop, but fails on PBI online once I do a merge/append/whatever in PowerQuery even though individual data sources are fine (gateway or direct credentials)

Hi. Just wanted to recheck if there is anything in place to fix this issue. It's a pretty big bug when you can't append/merge cloud data sources with on-premises even though both are being imported.

Can't refresh data after appending queries which are from different data source kinds

19 Replies

Anonymous
Not applicable
8 years ago
Anonymous

Anonymous

Hi, did you get your report to work at Power BI service - that is - get the automatic refresh working?

I have a similiar problem: I have a PBI workbook with SQL Server and documentDB data and I have appended a new query from these two data sources.

The automatic refresh was not completed and gives me an error indicating that the Appended Query table was the root of the problem (CRM_ERP_total_sales).

Any idea how to fix this? I am combining old sales data from SQL Server and combining it with the new sales data from Azure DocumentDB.

edit: I am using for this case the new Gateway in Personal mode and I have defined the data sources in Edit Credentials. I had to install this GW in Personal mode just for this report, because our existing On-premises GW did not function with both on-premises SQL server data and cloud data (Azure DocumentDB).

edit2: I could swear this worked yesterday when I tried to refresh the data in Power BI service On demand (it refreshed the data, there's 8 second difference what the dashboard tile refresh time shows and Refresh History), but now when I look the refresh history it says all of them failed, for the same reason
- Anonymous
  Not applicable
  8 years ago
  The work around solution for the moment is to list the file in your gateway. So rather than place the Folder in your gateway, you will need to list the exact files. Its awful but it works. It is what we are having to do until this issue is properally corrected.
  - Anonymous
    Not applicable
    8 years ago
    Anonymous
    
    Okay, thanks for the tip. Do you mean I have to add some exact file paths in the Power BI Gateway (Personal) application on the server machine which I have installed it? Do you know what is the name of executable file on the actual file drive? I feel like a dummy trying to find the actual new Power BI personal gateway files, since the file gateway paths & folder etc. are not very explicit IMHO
    
    Or do I have to define the exact DocumentDB colletions in Power BI Service as above (app.powerbi.com)? Now I have in a way just the root URL of our DocumentDB cloud database.
- nowhereman2302
  Frequent Visitor
  8 years ago
  I have a similar issue. However, in my case, I have only a single data source, my redshift db. The issue occurs while going for schedule refresh using on-premise gateway(personal mode). I am able to publish it from Power BI Desktop, but unable to schedule it from power bi online.
  
  It was mentioned in one of the troubleshooting docs of on-premise data gateway that you might have to install a data provider in the same system where the gateway is installed. But that too is not working. I have already got the 64 bit ODBC driver for Amazon Redshift installed in my machine. It still doesn't work.
Anonymous
Not applicable
8 years ago
Hi Everyone,

Can PBI support to run a query which appends two different data source queries now?

One of data source is from VSTS and the other is from SQL Server. I append these 2 queries as a new query.
It works well when I refresh on PowerBI Desktop, but it refreshs failed on PowerBI Service and notes me as below.

"The provider is not support or due to invalid data source. Please check the data source connection string."

Thank you in advence!

Regards,
Kelly
Anonymous
Not applicable
8 years ago
I'd query whethere VSTS is a valid data source for a gateway. I think thats going to be your problem. You can manually refresh things in your Power BI desktop that your gateway cannot. I know from my own projects that Active Directory is an example of a source that i've run into with this limitation.
- Anonymous
  Not applicable
  8 years ago
  Thanks for your reply!
  My data source credentials work well now and the message PBI notes is:
  "Underlying error message:The given data source kind is not supported."
  
  I have never heard Active Direstory before. Could you give me some introduction about this?
  Thanks a lot!
  - Anonymous
    Not applicable
    8 years ago
    Active Directory is what our Exchange client uses to keep track of everyone. It knows their computer, their email, their computer passwords. Its used for Single Sign on, which is the ability to log onto your computer and have that log-on act as authentication for other secure systems.
    
    I needed to report on our employee users and all of the data is contained within Active Directory. Sadly since the gateway can't refresh from it, i've had to rely on an SQL server that replicates the data for me.

Forum Discussion

Can't refresh data after appending queries which are from different data source kinds

19 Replies

Related content

Incremental refresh appending not replacing

Append Queries 3 different data sources

Dataflow - Append

Incremental Refresh with Appended Web Source

Appended queries not refreshing in service anymore