Google Sheets
Power Query is an important tool for data cleaning and transformation in Power BI. It allows users to connect to various data sources, including Google Sheets. Power Query’s M Language is a powerful scripting language used to customize data transformations.
In this article, we will demonstrate how to connect to Google Sheets data source from inside Power BI using Power Query’s M Language.
Prerequisites
Before we begin, ensure that you have:
A Google account
A Google Sheet with data
Power BI Desktop installed on your computer
Connecting to Google Sheets in Power BI
1. Open Power BI Desktop and click on ‘Get Data’ from the Home tab.
2. In the ‘Get Data’ window, select ‘Web’ and click on ‘Connect’.
3. In the ‘From Web’ window, enter the URL of your Google Sheet and click on ‘OK’.
4. A new window will appear, showing a preview of your data. Click on ‘Edit’ to open the Power Query Editor.
Writing M Language Code
1. In the Power Query Editor, select the ‘Add Column’ tab and click on ‘Custom Column’.
2. In the ‘Custom Column’ window, enter a name for your new column and paste the following M Language code:
= Table.FromColumns({Lines.FromBinary(Web.Contents(“YOUR_GOOGLE_SHEET_URL”))}, {“Column1”})
3. Replace ‘YOUR_GOOGLE_SHEET_URL’ in the code with the URL of your Google Sheet.
4. Click on ‘OK’ to create your new column.
Transforming Data
1. In the Power Query Editor, select the ‘Transform Data’ tab to start data transformation.
2. You can use various transformation tools available in Power Query Editor such as splitting columns, filtering data, and merging tables.
3. Once you are done with data transformation, click on ‘Close & Apply’ to save your changes.
Conclusion
In this article, we have demonstrated how to connect to Google Sheets data source from inside Power BI using Power Query’s M Language. By using M Language in Power Query, you can easily customize your data transformations and make your data analysis more efficient.
