How to change data source from EXCEL to SQL Server?

[Anonymous]

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

[Anonymous]

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).

[Anonymous]

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.

[GilbertQ]

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]

Thanks for the reply @GilbertQ.

Actually me too doing the same thing.

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

[GilbertQ]

Hi @Anonymous

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

[J_Tanna]

Hi @GilbertQ 

I am facing same problem. But not able to understand what you want to convey. Would you like to convey it in more detail? That could be a big elp for me.

Thank You.

[GilbertQ]

Hi @J_Tanna

What you would need to do, is to go into the Query Editor, and create a new connection to your SQL Server Table.

Then once that is created you would then go into the Advanced Editor and copy all the M Code.

Next you would then need to go to your Excel Source, and replace all the steps that refer to the Excel Source and then replace it with the copied code from the SQL Server table.

[MAAbdullah_47]

Hi @GilbertQ 

I need your help , I need more details about this step I have the same case for the posted question but you didn't show any details , could give some details on how to do this migration steps? 

[christianfcbmx]

hI @GilbertQ is there a tutorial ro something where to watch?,,,Im trying to do this and I cant.

Thanks in advance.

[GilbertQ]

Hi @christianfcbmx 

This blog post should assist you Changing Data Source Location in Power Query • My Online Training Hub

[GilbertQ]

Hi there

I do not know of anything off the top of my head, but that is a good idea for a blog post.