Forum Discussion

mahenkj2's avatar
mahenkj2
Icon for Solution Sage rankSolution Sage
2 years ago

Unable to combine data - multiple sources append

 

Hi, below is the scenario:

  1. A history table tblHistory with static data is present (sql server source).
  2. I retrieve LastDate from history table as text
  3. I generate a TodaysDate  as text
  4. Use LastDate and TodaysDate in two parameters namely StartDate and EndDate respectively.
  5. Use StartDate and EndDate in a web query WebLink, it works well and data is fetched.

If I publish the report here, report refreshes well. I have connected cloud connection for web query and on premise gateway for sql server datasource.

Problem is:

I need a combined table of WebLink and tblHistory. Both have same formats. As soon as I append both as a new table tblDaily, start getting following error during refresh in Power BI service:

  • Underlying error code-2147467259
  • Underlying error message[Unable to combine data] Section1/WebLink/Removed Columns references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

There is no error in Desktop version and report refreshes well.

 

I understand that there is some dependence on these query each other, but can’t find the logic, and anyway I need to combine both WebLink and tblHistory to see update data as a whole.

How to achieve that?

Need Help

3 Replies

  • mahenkj2's avatar
    mahenkj2
    Icon for Solution Sage rankSolution Sage
    2 years ago

    To make the error message complete:

     

    • Underlying error code-2147467259
    • Underlying error message[Unable to combine data] Section1/WebLink/Removed Columns references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
    • DM_ErrorDetailNameCode_UnderlyingHResult-2147467259
    • Microsoft.Data.Mashup.MashupSecurityException.DataSources[{"kind":"Web","path":"https://.....................................................................................................u0026elements=datetime%2Ctemp%2Cfeelslike%2Chumidity%2Cprecip%2Cpreciptype%2Cwindspeed%2Cwindspeedmax%2Cwindspeedmean\u0026include=days\u0026key=................................................................\u0026contentType=json"}]
    • Microsoft.Data.Mashup.MashupSecurityException.ReasonPrivacyError
    • Cluster URIWABI-INDIA-WEST-redirect.analysis.windows.net
    • Activity IDa9ad7b4d-a5c2-576d-07d4-ff4fd15be5ea
    • Request ID8d4f3a61-ca23-104a-da98-3fe54581b335
    • Time2023-12-20 15:48:09Z

    I have repeatedly checked that all sources are set Privacy as "organizational", and as suggested in similar questions, I have checked the report setting to 'Ignore privacy setting' as well.

     

    I am confused that is t kind of circular dependency issue or some privacy setting related one. Please help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, mahenkj2  

     

    According to your description, you have a failed refresh after merging data sources. In fact, you cannot combine an external data source with another query. If you really need to merge, you can rebuild this data combination, and the following are the steps to solve your problem:

    • Right click the Query in Excel’s Query window and choose Edit.

            

     

    • Duplicate the Existing Query
      The query in question here is “Purchase”
    • Right click “Purchase” and choose Duplicate.
    • Immediately rename the query to PurchaseList
    • Go to View –> Advanced Editor
    • Selected everything from the comma on the second line down to the last row of the query:
    • Delete and change the final line of “Merge” to “Purchase_Sheet” and click done.   

                     

    Go back and modify our existing query.  So, in that left pane you will again select the Purchase query.

    • Go to View –> Advanced Editor
    • Select the first two lines and delete them.
    • Put a new line after the let statement that reads as follows.

        Source = PurchaseList,

     

    Please refer to the following link for details on how to do this:

    Related Link: Power Query Errors: Please Rebuild This Data Combination (excelguru.ca)

     

    If this does not work, could you please share some sample data without sensitive information and help me to reproduce your problem.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • mahenkj2's avatar
      mahenkj2
      Icon for Solution Sage rankSolution Sage
      2 years ago

      Hi Anonymous ,

       

      Thank you for the response.

       

      I too eventually reached at this post. I understand that I need to separate the query which is fecthing to external source. My scenario is somewhat different in the sense, that I am using a web query with start and end date parameters (sql source) and not sure how to separate the sql source from web.

       

      After seeing your suggestion, I created a separate query combining multiple text portions of web query but not hitting directly it, using start and end date parameters from sql source. This query results in one text string.

       

      Then I used that query into another query = Json.Document(Web.Contents(Query3)).

       

      This is how I tried to separate the sql sourced date parameters from web source, but it did not help at all and same privacy error persists.

       

      I could not share sample file since the file have lot of sensitive data.

       

      Please suggest how should I resolve this issue.