Changing data source from Excel to PBI.

[Anonymous]

My first post. Maybe a silly question.

I have an existing perfectly run data model in Excel, which is located on a network drive.

Now I would like to run this model on my local machine.

What I did, I copied all related tables, and data sources, all of it on the local drive and preserved all file structure, thinking that I would only need to change the drive letter in "Data Source" in PBI.

I did. 

It did not work. I am getting an error on refresh "We cannot convert data binary to value" or something like that.

I went and changed the drive letter everywhere, in Data Source, in Quries Sample files, but I am still getting an error.

Can someone suggest a solution? Did you guys run into this ever?

Regards!

[v-yuezhe-msft]

@Anonymous,

Before you change data source in Power BI Desktop, do you get same error when refreshing the data model? What steps do you take to transform data in Query Editor of Power BI Desktop? Could you please share the excel file which I can test?

Regards,
Lydia

[Anonymous · Accepted solution]

Hi, thanks for replying!

Actually, I have figured it out.

What you need do, if you change your data path, you need not only to change the drive letter in the Data Source options, but also you have to go to Advanced Editor of Get data and in the Sample Files correct all those wrong references to the initial data source.

And then it works!

Before doing so I get this, even if changing drive letter in the Data Source Options.

DataSource.NotFound: File or Folder: We couldn't find the folder 'N:\Participation & Connection\DataSets\Relations Tables (Do not change)\Fee Schedule\*'.
Details:
N:\Participation & Connection\DataSets\Relations Tables (Do not change)\Fee Schedule

This is what's happening in the Sample 

let
Source = Folder.Files("N:\Participation & Connection\DataSets\Relations Tables (Do not change)\Fee Schedule"),
Navigation1 = Source{0}[Content]
in
Navigation1

So I change drive letter there to F and it works.

With other files same situation.

let
Source = Folder.Files("N:\Participation & Connection\DataSets\UW Weekly Reporting\UWVolume"),
#"N:\Participation & Connection\DataSets\UW Weekly Reporting\UWVolume\_UWVolume xlsx" = Source{[#"Folder Path"="N:\Participation & Connection\DataSets\UW Weekly Reporting\UWVolume\",Name="UWVolume.xlsx"]}[Content]
in
#"N:\Participation & Connection\DataSets\UW Weekly Reporting\UWVolume\_UWVolume xlsx"

See here, not only I needed to change first line to drive F, but in the advanced editor all of those following the first line.

Thanks it's solved. (By me )