Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

How to change data source from EXCEL to SQL Server?

Hello All,

 

Currently i have loaded all the files from my local disk to powerbi and those are all EXCEL sheets.

 

then i have imported those excel sheets to SQL SERVER.

Now i would like to change the source of my report to the SQL SERVER tables.

 

When i try to do that i didnt see any option regarding that.

 

Is there any way to do that.

 

Any help..

 

Thanks,

Mohan V

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

11 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi Anonymous

     

    First thing is to make a copy and work on the existing copy of the PBIX file so that you have a backup.


    What I have done in the past is to import your data as you would from the SQL Server Tables into a table in the Query Editor.


    The next steps involve using the Advanced Editor in the Query Editor, and what you would do is to copy all the steps from the SQL Server Tables query where it gives you all the column names.


    Then take this and replace it in your Excel Source tables. It can be a little tricky but that will work, I have done it quite a few times in the past.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for the reply GilbertQ.

       

      Actually me too doing the same thing.

       

      But i thought there will be a direct solution for this.

       

       

      • GilbertQ's avatar
        GilbertQ
        Super User
        8 years ago

        Hi Anonymous

         

        No problem I do not think that there currently is any easier way.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I used the method advised here to change data source from Excel to SQL Server.

    However, the challange I'm facing is changing the storage mode of the Excel Query that I changed to direct query as it still retained import mode.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Could you please describe a bit more in detail (using a very short example for example) how to do this? The links provided in the previous replies did not show how to do this when changing the source from Excel to SQL Server database (in Azure).