Added a column in excel data file and Power BI is giving an error after doing a refresh

[Rnaval]

Added a column in excel data file and now Power BI is giving an error after doing a refresh. I guess I don't know the correct process to add a column. How do I find the error and correct it?

[Rnaval · Accepted solution]

This issue has been resolved. It turned out to be a wrong date value that was causing the issue. I want to thank you for spending time and effort.

[aj1973]

I think I remember telling you to correct a date value (format) in a cell. But glad you made it and Welcome.

[Rnaval]

The column I had added was "Completion Status", it was added between the Phase and Start Date columns, it has since been removed but I still continue to get errors...

[aj1973]

I know, But it wasn't removed from Sharepoint.

[Rnaval]

Here are the links to my Power BI file and Excel data...

https://www.dropbox.com/scl/fi/v3blcs28ki3x10kx8gm16/OTProjects.pbix?rlkey=78944wg3i8dhjpe61gc2d8whq...

https://www.dropbox.com/scl/fi/8cvemwzvzn8qhnhp8ylrk/OT-Projects-Dash.xlsx?rlkey=ene9swjotelhujpipju...

[aj1973]

Please fix this cell in Row 211

09/30/2023 is not good Date Format. To do it Copy/Past the Start Date then replace 6 by 9. 

[Rnaval]

That was corrected, Thanks...

[aj1973]

Here you go

https://drive.google.com/drive/folders/10YJjGsglIawYcKRcxbvV49aPZUhQ-cWb?usp=sharing

You will have to change the URL string in Power Query by you URL
Open advanced editor and replace your URL here

As I mentioned earlier you need to fix the column End Date Format(The field format) check my previous post. Or you can just replace the file I shared here by yours in Sharepoint.

Again, the solution is going to work in your desktop however it will not work in the service when trying to refresh it. As I said earlier you did not use Sharepoint Folder connector, you used Web connector which is wrong.

Good luck

[Rnaval]

Ok, so in trying to correct the connection, I used the pbix file you sent and I get the following prompt. 

[aj1973]

You can't use my Sharepoint, it won't connect. You need to open Power Query and do what I told you to do in my previous message. "Change the my URL by your URL" in the M code.

[aj1973]

I see the issue, You are not connecting the right way to Sharepoint. You are using a Web connector to connect, False.

Do you want me to change the connectors for you?

[Rnaval]

If not a web connection, what kind of connection should it be?

[aj1973]

Sharepoint Folder

but you are gonna need some technical skills to do it on the same pbix Or which i recommend start from scratch with a new pbix and then be patient to Copy/Paste all you report.

[Rnaval]

I want to try and use the same pbix and switch the data source, how do I do that? Can you advise the step by step instructions for me to follow? The option you suggested seems more complex to me trying to create this from scratch with a new pbix and then bringing in all of the visuals and formatting the fields, etc

[aj1973]

Hi @Rnaval 

I just uploaded the pbix file with Sharepoint connector, check it out.

All you need to do

• Open Power Query
• select table Sheet1 (2)
• Click on Advanced Editor to open the M Code
• replace this part in yellow

  by your URL Sharepoint "https://nymta.sharepoint.com/sites/VulnerabilityManagementDashboard/"

• Apply, Then you should see the table in Power Query
• Do the same thing for the table Program Status.

Let me know please

[Rnaval]

I checked my current pbix and It's already using the sharepoint location -

Source = Excel.Workbook(Web.Contents("https://nymta.sharepoint.com/sites/VulnerabilityManagementDashboard/Shared%20Documents/General/OT Projects/OT Projects Dash.xlsx"), null, true),

I don't see the file you uploaded, the only pbix file on dropbox is from yesterday at 11:09 am

[aj1973]

It's been there, please reconnect to my Drive and you will find it.

Also please follow instructions from my last message and you will succeed.

Good luck

[Rnaval]

Ok, found it. When I go into Advanced Editor - I get the following

[aj1973]

Please go back to message 11 and follow the instructions, you will be good.

[Rnaval]

I tried to change the string to my sharepoint location - https://nymta.sharepoint.com/:x:/r/sites/VulnerabilityManagementDashboard/Shared%20Documents/General..., but no luck.

I even tried this string but no luck - https://nymta.sharepoint.com/:x:/r/sites/VulnerabilityManagementDashboard/

I get this error -

DataSource.Error: Web.Contents failed to get contents from 'https://nymta.sharepoint.com/sites/VulnerabilityManagementDashboard/_api/web/lists/getbyid('22a6e75d...' (405): Method Not Allowed
Details:
DataSourceKind=SharePoint
DataSourcePath=https://nymta.sharepoint.com/:x:/r/sites/VulnerabilityManagementDashboard
Url=https://nymta.sharepoint.com/sites/VulnerabilityManagementDashboard/_api/web/lists/getbyid('22a6e75d...