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

 

Thanks,

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 

6 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AnthonyTilley,

      Can you please give me some insights on given process as unable to get the desire results according to suggested method.

       

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi Maruti_K,

        I think AnthonyTilley's suggestions is tell yo to create a new table(get data->Excel). And if your SQL and Excel's data and column names are same, you could rename SQL table to another name, and name Excel as original name, then the measures you create based on SQL table will work for Excel.

        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.

         

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