How to change the data source from SQL to Excel

[Anonymous]

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,

[dax]

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.

[AnthonyTilley · Accepted solution]

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]

Hi AnthonyTilley,

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

[dax]

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.

[Anonymous]

Hi Zoe Zhi,

Do I need to work again on the relationship part,  If rename the columns and import the data in excel with same structure. 

I observed that the relationship is remain same with table and fileds which was earliar assign to old tables.

According to the statement it should switch to latest once rename is done for all the relavant tables.

Please guide.

Thanks,

Maruti K

[dax]

Hi Maruti_k,

I think you need to map the relationship manually after you create new table, relationship won't automatically be changed. But you don't need to change measure(after you re-map the relationship, measures will work correctly).

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.