Resolving Power Query error “Specified value has invalid CRLF characters.Parameter name: value” when connecting with SharePoint on-premises site

In Microsoft Office Excel Power Query, we have an option to connect with SharePoint lists. When I tried to test the functionality with on-premises SharePoint sites it was always giving me the error “Specified value has invalid CRLF characters.Parameter name: value”.

Following were the steps I used to connect with my SharePoint site

Unfortunately this was the error I got for all my on-premises environments

I changed web application settings and enable anonymous authentication for that web application, but nothing worked for me. Then I tried with a SharePoint online environment. It worked perfectly!! So was that due to a SharePoint build difference?

I checked my on-premise SharePoint patch level. they were all patched with November 2014 CU. Ok, that was pretty old !!

Then I updated one of my SharePoint environments with the latest CU, which is the March 2016. You can download the CU from this location. You can read the relevant KB article from following location

After the update I was able to successfully connect to my SharePoint environments.