cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AkhilT
Regular Visitor

Google Sheet connector for Power BI is not working

I am trying to connect my dataset via a google sheet. It worked fine until yesterday, but today it is throwing this error.

AkhilT_0-1658679332915.png

Can some help me with this issue?

1 ACCEPTED SOLUTION

Hi @v-cgao-msft ,

Thank you for the response; it was partially helpful.

Fortunately, I found a solution for this issue, dropping the link for reference.

 

https://www.youtube.com/watch?v=aCsg-uefMqk

View solution in original post

5 REPLIES 5

Try this steps 

 

Step 1: Publish your Google Sheets as a web page

First you must share the document. Choose “Anyone with the link can VIEW”
Then in the menu choose “File” => “Publish to the web”
Choose “Entire document” and “Web page”, click Publish
Copy the URLs for each sheet into f.e. Notepad. For each table (Exercise, Weight, Health, My Day, Time), choose the sheet and copy the link

 

Step 2: Enter the data into Power BI

Observe that you need to use Power BI Desktop, and NOT the Power BI web interface.

Open Power BI Desktop and create a new file
Click the button “Get data” at the top and choose “Web” as source.
In the dialogue box, choose one of the URLs from Google SheetsGoogle-URL.jpg
In the data source dialogue box, choose the Google Sheets URL as a data source
In the “Navigator”-popup, notice that the data lies in “Table 0”. Check the table and click the “Load”-button.
The fields pane after adding first table
Now you have loaded your first table. In the Fields panel it will look something like the image on the right. Rename the Table “Exercise”.
Do the same procedure with the other 4 tables and rename them accordingly.


Step 3: Clean the data

Now we have 5 tables loaded, but there are a number of issues: The column names aren’t what we want them to be, there are columns with no data and the columns have default data type string.

Click “Edit queries” in the top menu to open the Query Editor:
Edit-queries.jpg
Choose the “Exercise”-table
Choose “Use First Row As Headers” in the top menu. As you might notice, in the “Applied steps”-panel, a new row “Promoted Headers” will appear. If you make a mistake you can always remove steps here
Remove the first column which is empty and the second column with rownumbers, and remove any null-columns to the right
Correct decimals. If you have any columns with decimal numbers, you might have to do a simple replace-action. (Google uses ‘.’ (dot) while Power BI uses ‘,’ (coma). This might be different in other language editions. Anyway, we have to change any decimals with dots to coma. Choose the column/columns that have decimal numbers. Choose the “Transform”-tab at the top and click “Replace Values”. Now replace ‘.’ with ‘,’. Notice that the Datekey-column should also be a whole number.
In our case the rest of the columns should have type “Whole number”
We do the same procedure for our other tables. (In the Time-table the Datekey column should be a Whole number, while the Date column which should have a real date-format should have format Date. Other fields such as Year, Month and Day should normally be text fields, while offset-fields should be Whole numbers)
Now all the data is clean and in a nice format. Power BI will understand that Datekey is the common key, and will automatically join the tables on this column. The Fields panel will now look something like the screenshot to the right. Now we can create some visualizations and reports!

 

Regards,

Rachel Gomez

v-cgao-msft
Community Support
Community Support

Hi @AkhilT ,

 

1.  Please try to install and use the latest version of Desktop:Downloads | Microsoft Power BI

 

2. Try to click "File"->"Options and Settings"->"Options"->"Data Load"-"Clear cache".

 

3. Try clearing the permission and re-entering it. “Transform data”->"Data source setting"->"Clear permissions".

 

If the problem persists, please feel free to contact me.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Hi @v-cgao-msft ,

Thank you for the response; it was partially helpful.

Fortunately, I found a solution for this issue, dropping the link for reference.

 

https://www.youtube.com/watch?v=aCsg-uefMqk

AkhilT
Regular Visitor

Thanks, @ribisht17 

Password and authentication look fine; it seems to be an issue with OnPremise Gateway

ribisht17
Super User
Super User

@AkhilT 

 

Please check your Gateway page in Power BI service and check for the Password and Authentication section, you may get something there

>>Manage your data source - Import/scheduled refresh - Power BI | Microsoft Docs

>>Troubleshooting refresh scenarios - Power BI | Microsoft Docs

Regards,

Ritesh

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.