Web
Power Query is an ETL (Extract, Transform, Load) tool that is built into Power BI. It allows users to connect to a wide variety of data sources and transform the data before loading it into Power BI. One of the most powerful features of Power Query is that it allows users to connect to Web data sources. In this article, we will discuss how to use Power Query’s M language code for connecting to Web data sources from inside Power BI.
What is Power Query M Language?
Power Query M Language is a functional language that is used to transform data in Power Query. It is similar to Excel formulas, but with more advanced functionality and syntax. M Language is used to create queries in Power Query, and can be used to connect to a wide variety of data sources, including Web data sources.
Connecting to Web Data Sources with Power Query M Language Code
To connect to a Web data source from inside Power BI, we first need to understand the format of the Web data source’s URL. The URL should contain a unique identifier for the data that we want to retrieve. This unique identifier is typically called a “query string parameter”. For example, if we wanted to retrieve data from the Yahoo Finance Website, we could use the following URL:
https://finance.yahoo.com/quote/AAPL/history?p=AAPL
In this URL, the “query string parameter” is “AAPL”. This tells the Yahoo Finance Website to retrieve the historical stock price data for the Apple Inc. stock.
Once we have the URL for the Web data source, we can use Power Query’s M language code to connect to the data source and retrieve the data. Here is an example of the M language code that we can use to connect to the Yahoo Finance Website and retrieve the historical stock price data for Apple Inc.:
let
Source = Web.Page(Web.Contents(“https://finance.yahoo.com/quote/AAPL/history?p=AAPL”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Date”, type date}, {“Open”, type number}, {“High”, type number}, {“Low”, type number}, {“Close”, type number}, {“Adj Close”, type number}, {“Volume”, Int64.Type}})
in
#”Changed Type”
Let’s break down this code:
– The “let” statement is used to define a variable called “Source”
– The “Web.Page” function is used to retrieve the HTML source code of the Web page
– The “Web.Contents” function is used to specify the URL of the Web data source
– The “Source{0}[Data]” statement is used to extract the data from the HTML source code
– The “#” sign is used to indicate that we are applying a transformation to the data
– The “Table.TransformColumnTypes” function is used to convert the data types of the columns in the table to the appropriate types (date, number, etc.)
Once we have executed this code, we will have a table of historical stock price data for Apple Inc. that we can use in our Power BI reports.
Conclusion
Power Query’s M language code is a powerful tool for connecting to Web data sources from inside Power BI. By understanding the format of the Web data source’s URL and using the appropriate M language code, we can easily retrieve and transform data from a wide variety of Web data sources. With this knowledge, we can create more powerful and insightful Power BI reports that can help us make better business decisions.
