Change data source of an existing Dashboard from Local folder to Sharepoint

[Anonymous]

Hello everyone!

I have an issue trying to change the data source of a dashboard I created months ago (with a lot of effort and hours).

Until now my client gave me the data from local folder, but now he wants to connect the PBI to a Sharepoint site. Well, I had no problems to connect to the site and find the data following this steps I found in another post:

1. Open the dashboard in Power BI Desktop and go to Power Query Editor

2. Add a new data source New Source > More... > SharePoint Folder. Use only the base URL for your SharePoint site (top level only). This will bring up a list of all files in your SharePoint. 

3. Transform Data - this will create the base query (don't bother renaming it)

4. Filter folders if necessary to find your Excel file. Once you find it, click on Binary then click 
Table next to the tab you want.

5. Click Use First Row as Headers in the ribbon.

6. Go to the Advanced Editor and copy all the code you just created in previous steps

7. Now go to your original query that points to the Excel file on the local network.

8. Go to Advanced Editor for that query, select all and paste the previously copied code over the top.

9. Click OK. Your original query is now pointing to the SharePoint file and no links will be broken.

I found them a solution to do it but I'm having some problems from step 8. In that step it suggests to select all my old code (the one that "cleans" all the data that shows already my finished dashboards) and paste the new code we get after transform the connection with the file located in sharepoint (did it on steps 4,5,6 and 7... but what's happening with the old code? I need to maintain this code to preserve the cleaned data when the data source was in local folder... but how can I integrate this new code and the old code so I don't get errors? I'm not sure where I have to paste the new code... I was trying to replace the old location by the new location but it doesn't work... it gives me errors...

Here you can see the new code and the old one together:

Could anyone help to preserve the old code so I don't have to work again on all the Transform data I did it previously?

Thank a lot

Ana M

[v-kkf-msft]

Hi @Anonymous ,

This is because you are using the same step name "Filas filtradas". Please change the second "Filas filtradas" to "Filas filtradas 2" and change the reference name in the subsequent steps.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

Thank so much you for your answer.

Well, now my problem it is a bit different...

Until this morning I finally dealed with that error, now I found a bothering issue that I tryied to fix but is not working for now.

When I loaded the file from local carpet, power query detected all the columns (even the ones that where with no data, so I cleaned them in the beginning of my transformations in Power Query.

The thing is that when I load the data from the Sharepoint Site, for some reason power query ignore this columns that have no data and now I have 2 columns less than in my previous queries, so I get errors and the columns doesn't match any more. Here the difference between the data loaded from carpet folder:

As you can see column1 and column2 have no data, so I deleted them in my old query.

And the loaded data from Sharpoint site:

As you can see column1 has already data so it's corresponding to column3 of the old query, so all the other (and old) tables will not match with the new ones anymore.

I tryied to create 2 fake new tables to put them in the beginning and recreate the old query:

But when I copy-paste this new code in the beginning of the old code to change the data source from local folder to sharepoint the error message of missing column is still there...

Any help please...?

Thank you so much!!

Best regards,

Ana M

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

If you set the datasets in Excel sheet on SharePoint as Table(select the whole range of rows and columns and ctr + t) and then import the table in Desktop, it should be able to ensure the integrity of the data, i.e. import empty columns.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much, problem solved!