Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Connect to Google Sheets

I connected Power BI using the Web option to Google Sheets. Works OK, but, the 1st row of sheet contains the header, Power BI doesn't recognize this and labels all columns Column1, Column2....ColumnN. I can manually rename them by adding steps in the Edit Query Window, but, this is not good as new columns will not be retrieved if the source changes. How can I force Power BI to recognize 1st row of data as headers? I don't see any options in the connection to do this.

 

This is how my connection looks :

 

= Web.Page(Web.Contents("https://docs.google.com/..../pubhtml", [Timeout=#duration(0, 0, 5, 0)]))
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous,

    Instead of renaming all columns, you can click "use first row as Headers " option in Power BI Desktop query editor.


    Regards,
    Lydia

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous,

    Instead of renaming all columns, you can click "use first row as Headers " option in Power BI Desktop query editor.


    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks. This works great, I am now getting my column headers correctly. The issue I now have is that the order in which the columns appear in Edit Queries window is the correct one (same as in Google Sheets), but, when you view the table in the Data Window columns appear in Alphabetical order. In query editor I only connected to Google sheet, and then promoted headers to column names. Since I have many columns, and I need to create calculated columns for most, then it's very difficult as I don't know exactly what column I am working against.