Datamarts
When it comes to data analysis, Power BI is a popular tool used by professionals to extract insights. However, to use Power BI to its full potential, it is important to know how to connect to a data source. In this article, we will focus on how to connect to Datamarts Data Source from inside Power BI using Power Query M Language Code.
What is Power Query M Language Code?
Power Query M Language Code is a language used by Power BI to extract, transform, and load data from various sources. It is a programming language that is used to create custom functions, load data, and perform other transformations on data.
Connecting to Datamarts Data Source using Power Query M Language Code
To connect to Datamarts Data Source using Power Query M Language Code, follow the steps below:
1. Open Power BI Desktop.
2. Click on “Get Data” from the home tab.
3. Select “SQL Server” from the list of data sources.
4. Enter the server name, database name, and credentials for the Datamarts Data Source.
5. Click on “Connect” to connect to the Datamarts Data Source.
6. Once connected, click on “Edit” to open the Power Query Editor.
Writing Power Query M Language Code
To write Power Query M Language Code to connect to Datamarts Data Source, follow the steps below:
1. In the Power Query Editor, click on “New Source” from the “Home” tab.
2. Click on “Blank Query”.
3. In the formula bar, type the following code to connect to Datamarts Data Source:
let
Source = Sql.Database(“ServerName”, “DatabaseName”, [Query=”SELECT FROM Table”]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}, {“Column2”, type number}})
in
#”Changed Type”
4. Replace “ServerName” with the name of your server, “DatabaseName” with the name of your database, and “Table” with the name of the table you wish to query.
5. Click on “Done” to save the code.
Querying Data from Datamarts Data Source
To query data from Datamarts Data Source using Power Query M Language Code, follow the steps below:
1. In the Power Query Editor, click on “New Source” from the “Home” tab.
2. Click on “Blank Query”.
3. In the formula bar, type the following code to query data from Datamarts Data Source:
let
Source = Sql.Database(“ServerName”, “DatabaseName”, [Query=”SELECT FROM Table”]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}, {“Column2”, type number}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([Column2] > 100)),
#”Grouped Rows” = Table.Group(#”Filtered Rows”, {“Column1”}, {{“Count”, each Table.RowCount(_), type number}}),
#”Sorted Rows” = Table.Sort(#”Grouped Rows”,{{“Count”, Order.Descending}})
in
#”Sorted Rows”
4. Replace the “ServerName”, “DatabaseName”, “Table”, and column names with your own values.
5. Click on “Done” to save the code.
Conclusion
Connecting to Datamarts Data Source using Power Query M Language Code is a powerful way to extract insights from your data. With the steps and code provided in this article, you can easily connect to Datamarts Data Source and query data using Power Query M Language Code.
