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.

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.

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=xlsx&id=13094301

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 url5. then add export?format=xlsx&id= where the edit/? had previously started6. then copy and paste the long id from the first part of your url7. 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

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

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.

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

21 Replies

MendelO
Regular Visitor
7 years ago
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
  Regular Visitor
  6 years ago
  I had the same issue, removing the pivot table from the Google Sheet fixed it for me.
  - airwreck
    Frequent Visitor
    6 years ago
    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
Frequent Visitor
7 years ago
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=xlsx&id=13094301
Anonymous
Not applicable
7 years ago
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
emilhizer
Frequent Visitor
7 years ago
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.
yperez
Frequent Visitor
7 years ago
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=xlsx&id=13094301
krcarlosm1990
Regular Visitor
3 years ago
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.
GilbertQ
Super User
9 years ago
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
  Frequent Visitor
  9 years ago
  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.
v-jiascu-msft
Microsoft Employee
9 years ago
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
  Frequent Visitor
  9 years ago
  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
    New Member
    8 years ago
    Hi!
    
    I have the same problem. Trying to import google sheets to Power BI and have this messege.
    Did you solve it somehow?
Jannat
Frequent Visitor
8 years ago
I am having exactly the same error. Is there anyway to solve it?
- makency
  New Member
  8 years ago
  tambien tengo el mismo inconveniente cual es la fuente que lo genera
makency
New Member
8 years ago
Buenas tardes quisiera saber si ya tiene solucion para este error

Gracias.