AnalysisServices.Database

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function AnalysisServices.Database

At the heart of Power Query lies the M language, a functional programming language used to write queries. The M language is used to perform a variety of tasks within Power Query, from importing data to transforming and shaping it. In this article, we’ll delve into the M code behind the Power Query M function AnalysisServices.Database and explore how it can be used to analyze data.

What is AnalysisServices.Database?

AnalysisServices.Database is a function within Power Query that allows you to connect to and query data from SQL Server Analysis Services. Analysis Services is a tool provided by Microsoft that lets you create and manage multidimensional data models and OLAP cubes. It can also be used to create tabular data models, which can be queried with Power Query.

How to use AnalysisServices.Database

To use AnalysisServices.Database in Power Query, you first need to connect to your Analysis Services server. This can be done by clicking on the “Get Data” button in the Home tab of the Power Query ribbon, selecting “SQL Server Analysis Services” from the list of available data sources, and entering the server name and any necessary credentials.

Once you’ve connected to your server, you can use the AnalysisServices.Database function to query your data. The function takes three arguments:

– Server: The name of the Analysis Services server that you want to query.

– Database: The name of the database that you want to query.

– Options: A record containing any additional options that you want to pass to the function.

Here’s an example of how to use the function to query data from an Analysis Services database:

let

Source = AnalysisServices.Database("localhost", "AdventureWorksDW2017", [Query="SELECT FROM FactInternetSales"])

in

Source

This code connects to the local Analysis Services server, selects the AdventureWorksDW2017 database, and queries all the data from the FactInternetSales table.

The M code behind AnalysisServices.Database

Under the hood, the AnalysisServices.Database function is powered by M code. The M code behind the function is responsible for connecting to the Analysis Services server, querying the database, and returning the results to Power Query.

Here’s an example of the M code behind AnalysisServices.Database:

let

Source = AnalysisServices.Database("localhost", "AdventureWorksDW2017", [Query="SELECT FROM FactInternetSales"]),

#"Expanded Table" = Table.ExpandTableColumn(Source, "InternetSales", {"OrderDateKey", "ProductKey", "CustomerKey", "SalesOrderNumber", "OrderQuantity", "UnitPrice", "ExtendedAmount"}, {"OrderDateKey", "ProductKey", "CustomerKey", "SalesOrderNumber", "OrderQuantity", "UnitPrice", "ExtendedAmount"})

in

#"Expanded Table"

This code does the same thing as the previous example, but it also expands the InternetSales column to show the individual columns within the table.

The AnalysisServices.Database function is a powerful tool within Power Query that allows you to connect to and query data from Analysis Services. The M code behind the function is responsible for handling the connection and query, and can be customized to suit your specific needs. By understanding how AnalysisServices.Database works and how to use it, you can unlock the full potential of Power Query and take your data analysis to the next level.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)