Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to change the data source from SQL to Excel

Hello Community,   I tried to change the data source from SQL to excel for existing report but couldnt able to find any option related to excel under Change Source.   Can anyone please guide on t...
Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    If your table is identical (same names for each colunm)

     

    then the best option is to go to the query editor

    connect to the new table in excel 

    rename your old SQL table to TABLE_OLD

    Rename yor new table to be exactly what the old one was 

    and then delete the old table.

    when you then click close and apply the new table will be treated as if it was the old and all your measures and visuals will swap to this table as the relationships are based on table name 

dax's avatar
dax
Community Support
6 years ago

Hi Maruti_K,

It seems that change datasource will change the same type datasource, so if you want to change datasource from SQL to Excel, you could try to change this by edit queries->Advanced Editor by M query

let
    Source = Excel.Workbook(File.Contents("C:\test\Book1.xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"name", type text}, {"values", Int64.Type}})
in
    #"Changed Type"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.