Data Source Changes - The remote server returned an error: (400) Bad Request. (Bad Request)

[PaulaMcC]

Hi All,

I've recently started at a new company as a Data Analyst and having some problems with data sources in one of the reports. The original data sources are located on my predecessor’s personal drive. She has moved everything to SharePoint but forgot to change the data source in the report. The problem I'm having is identifying which folder/spreadsheet her queries are coming from and keep getting the error The remote server returned an error: (400) Bad Request. (Bad Request).
Any tips or tricks for working backwards like this? I'd prefer not to have to rebuild it at this stage!
Any advice or guidance would be appreciated.
Thanks

**Edited to add it looks like the original source was a SharePoint List as it contains: _vti_bin/ListData.svc

[edhans]

You cannot change sources in the UI from one type to another. So moving from her C:\ to your C:\ would work fine, but not from your C:\ to a SharePoint list or Sharepoint folder.

What you will have to do is this:

1. Connect to the new location
2. Make the new table/query appear exactly as the old one.
3. Once it looks the same (exact same column names - including same letter case -  and data types) you would go into the Advanced Editor and copy all of that M code to the old query's M code replacing it.

You could also copy transformations from the old the new but that is a bit trickier to do this and may or may not save time. How to use M code provided in a blank query - this blog post will help with that: See this article if you need help using this M code in your model.

The reason you have to do it this way is all of your tables are loaded in Power BI, and then you have measures and visuals on top of those. Power BI doesn't actually use table names. It uses table identifiers. So even if you had a table called "Sales" connected to her C drive, and you deleted that and created a new table called "Sales" the identifier would be different, and everything in DAX would break. This is why you replace the code inside the tables so the table stays the same.

Bottom line is you can definitely do this, but it isn't button clicks. It is tinkering with some M code.

[PaulaMcC]

Thank you edhans. I keep coming back to this problem but unfortunately I haven’t been able to make it work. I understand your solution but I can’t make a copy of the table or data. I’ve had to push it up the chain to IT to see if they can find the original data. I appreciate your help and getting back to me.