Connect to Google Sheets

[Anonymous]

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)]))

[v-yuezhe-msft · Accepted solution]

@Anonymous,

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


Regards,
Lydia

[Anonymous]

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.