'Relationship Tag' error when trying to connect a google sheet to Power BI.

[sushantj]

Hello Everyone, 

I was trying to connect a google sheet to Power BI so that i could extract data from the same. But i got a weird message from Power Bi saying - "Relationship tag contains incorrect attribute. Line 2, position 86." Refer the screenshot for the same. 

This was weird because i have tried to connect a google sheet to Power BI earlier but did not face this problem before.

Could anyone help me with this?

Thanks a lot in advance.

[krcarlosm1990]

It's also important to verify if your data base (excel, google sheets) constains filters actived.
If some filter is active, this can cause the same error. Just clean all your filters, save and close the file. Try again.

[MendelO]

Did you add a pivot table to your google sheets?

I encountered the same issue, I deleted the pivot table, refreshed the sheet then refreshed the connection in power query, worked like a charm.

Alternitavely, you can create a new google sheet and use Importrange, get the .xlsx download link and connect to that.

[ckraus]

I had the same issue, removing the pivot table from the Google Sheet fixed it for me.

[airwreck]

That's actually the issue here. Pivot tables don't work for some reason.

If you need to keep the table there though like I did, export as a CSV instead by using

export?format=csv&id=

[yperez]

Hi,

I was having the same issue. What fixed this for me was using /export?format=xlsx&gid=130923075 where gid you can get it from the URL of your google sheet. In my case, my google sheet had several tabs including some with Pivot tables and graphs which seem to be causing the issue. Thus, to export just the tab needed, click on the tab and get the gid to be used as shown above. For example the complete URL format will look as below:

https://docs.google.com/spreadsheets/d/1X2RwWGI7412fKLPuyPJg5GHeoTWPrxBTRtvuHJ7Q0nDgo/export?format=...

[yperez]

Hi,

I was having the same issue. What fixed this for me was using /export?format=xlsx&gid=130923075 where gid you can get it from the URL of your google sheet. In my case, my google sheet had several tabs including some with Pivot tables and graphs which seem to be causing the issue. Thus, to export just the tab needed, click on the tab and get the gid to be used as shown above. For example the complete URL format will look as below:

https://docs.google.com/spreadsheets/d/1X2RwWGI7412fKLPuyPJg5GHeoTWPrxBTRtvuHJ7Q0nDgo/export?format=...

[emilhizer]

This always seems to occur when I have a pivot table somewhere (anywhere on any tab) in the google sheet, even if I'm not importing that tab to Power BI. Tested by removing the tab that I added that had a new pivot table, and Power BI started loading my sheet again.

[Anonymous]

I just encountered this error myself. The simple way of solving this is to remove any pivot tables in the google sheet, after downloading the file. 
This has worked for me.

JM

[makency]

Buenas tardes quisiera saber si ya tiene solucion para este error

Gracias.

[Jannat]

I am having exactly the same error. Is there anyway to solve it? 

[makency]

tambien tengo el mismo inconveniente cual es la fuente que lo genera

[v-jiascu-msft]

Hi @sushantj,

I found something here: relationship-tag-contains-incorrect-attribute-line-2-position-86 , which could help.

"Sometimes xlsx files, generated by the third side vendors, have errors in XML schema. Try to open this file in Excel and immediately save it, then close."

For more infromation: https://productforums.google.com/forum/#!msg/docs/Pi1zSLYeE0I/1C0YpBuqAwAJ

Best Regards!

Dale

[sushantj]

Hi @v-jiascu-msft,

This one is a good solution. But, i am trying to connect the actual google sheet to Power BI so that i can automate my process. As the data gets updated in the sheet, it will get updated in Power BI.

Downloading google sheet as an excel file will not serve that purpose of mine. 

Thanks for your search though.

Regards,

Sushant

[Kristy]

Hi!

I have the same problem. Trying to import google sheets to Power BI and have this messege.
Did you solve it somehow?

[sushantj]

Hi @Kristy, 

As far as i remember, the problem got solved automatically. 

I followed the following steps to connect google sheets to Power BI.

I think it now takes a little more doing than just the "&output=xls" on the end of URL. Here's a  robust solution that I've tested and used quite a bit to get full data out of Google Sheet. Apologies for all the steps: 

1. Use Power BI desktop (this won't work just on Power BI service you have to start on desktop).

2. Share Google Sheet and get link from sharing.

3. Paste Google Sheet shared link and it will end in something like "adfe/edit?usp=sharing"

4. Remove the /edit?usp=sharing off the url

5. then add export?format=xlsx&id= where the edit/? had previously started

6. then copy and paste the long id from the first part of your url

7. the long, final URL you should use for Power BI get from web will be something like:

"https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad"

NOTE: id after equals sign matches id from Google for share sheet. (BTW  this isn't a real link just demonstration).

That's it - now you can design in Power BI desktop and publish to Power BI service on web (if needed). Only downside is there's no automatic refresh. Folks can edit / enter on Google Sheet but change won't appear in Power BI Desktop until you click refresh and won't appear in Power BI Service until you republish and overwrite. To attempt quasi-automation from a Google Sheets data source, you might want to consider saving your PBIX desktop file in your OneDrive folder since the Power BI service could update that hourly, that could potentially at least eliminate the final step in refreshing Power BI service. As soon as folks make changes to Google Sheet, you simply click refresh in Power BI Desktop which will fetch and refresh visuals based on updated data, but then just save that updated PBIX file in a OneDrive folder that is published to Power BI service and it should update automatically within an hour.

Sushant

[Kristy]

Thank you for such full answer!)

I'll try it.

[Kristy]

Hi!

I have the same problem. Trying to import google sheets to Power BI and have this messege.
Did you solve it somehow?

[GilbertQ]

I would suggest possibly creating a basic Google Sheet and see if that will connect successfully.

If that does work then there must be something different in the Google Sheet you are trying to connect to which you would need to try and go in a step-step manner to see what is causing it.

[sushantj]

Hi @GilbertQ,

I did that and tried to connect it to Power BI. I could do it successfully. 

Yeah, even i think there is something different with the google sheet which i am trying to connect. 

Thanks for the suggestion though.