🎯 Create a first query.

Open Power BI Desktop and load a pbix file containing the data you want to connect to.

In Excel click the connect button in the pbixl tab.

Please select a measure and some columns.

🔎 Filter data

Filtering can be done by selecting elements or by providing a search term.

A search term can be created using the AND and OR operators in conjunction with brackets.

Return "amounts below 1000 and between 10000 and 20000" would be written as: <1000 or (>=10000 and =<20000)

On date columns just write 2024 to get all dates in 2024.

Text columns can be filtered by asterisks.

The filter dialog provides an info icon. Click to see sample search terms.

🔀 Implicit measures

Standard functions like sum(), max(), count() can be applied to columns

Click on the left symbol of the column to select functions.

In the top right corner is an info button. Click here to inspect the created DAX statement.

📐 Create a Pivot Table.

In Excel click the connect button in the pbixl tab.

Please click the arrow on the OK button and select pivot table.

Click ok.

⚡ Make a reusable connection from an Excel workbook to a Power BI Desktop semantic model.

Every time you close and open Power BI Desktop the connection changes.

pbixl tries to reconnect the Excel workbook to the changed PBI Desktop connection.

When reconnecting Excel please open Power BI first. Then the Excel workbook.

Give a Power BI model a unique name by creating a hidden measure in the model.

pbixl = "myContoso"

🌍 Connect to Power BI Service or SSAS Tabular Model.

Provide a connection to PBI Service or a SSAS Tabular model in your workbook.

Get a connected workbook from PBI Service by using the Analyze in Excel feature.

Connect a workbook to SSAS Tabular by using the Get Data feature of Excel.

The connection appears in the connect dialog of pbixl.