Refreshing data from connected dynamic worksheet

[KMZ_ESS]

Dear all,

I've exported a dynamic worksheet from our CRM. The data refresh within the dynamic worksheet works. However, when I want to refresh the data in PowerBI, I'm getting an error that I can't make sense of (see below). I didn't change anything in the dynamic sheet. The "name" column is still there. I only refreshed the data.

I tried refreshing in PBI before refreshing in Excel and once after refreshing in Excel. Both times I'm getting the same error. Any idea what could cause this?

Many thanks!

[JamesCreasey]

Hi, is there a hanging space at the front of the column name and that is giving you issues? In the screenshot it looks like '_Name'. I wonder if somewhere between CRM, Excel and PowerBI, there is a trim function that's taking it from '_Name' to 'Name' and that's the issue.

[KMZ_ESS]

Hi,

you were right. In PBI there was a space before "name". But after I corrected it I still had the error. I don't get it. 😞

[KMZ_ESS]

If I change the column name in PBI to "Name" I'm getting the error. If I change the column to " Name" in Excel it changes back to "Name" when I refresh in Excel. What I haven't tried is changing to " Name" in Excel and only refreshing in PBI, not in Excel. 

[KMZ_ESS]

That didn't work either. 

[JamesCreasey]

I suspect what is happening is that the file is being exported from CRM with the name "_Name" column header.  When you open the Excel file, Excel is being 'helpful' and trimming the hanging space for you.  When you save the Excel file, there is no longer a space.

I'm sure there will be a way of dealing with either permutation in PowerQuery.  I wonder if this approach would do it for you.
Trim or remove spaces on column headers with Power Query – Curbal