## What lineage does Atlan extract from Microsoft Power BI?

URL: https://docs.atlan.com/apps/connectors/business-intelligence/microsoft-power-bi/references/what-lineage-does-atlan-extract-from-microsoft-power-bi

> This document helps you understand how Atlan generates lineage to upstream SQL sources for your Microsoft Power BI assets using a custom query parser, and the steps you can take while developing reports and dashboards in Microsoft Power BI to create seamless lineage generation.

Atlan currently supports the following lineage for [Microsoft Power BI](https://docs.atlan.com/apps/connectors/business-intelligence/microsoft-power-bi/how-tos/set-up-microsoft-power-bi):

- Lineage between Microsoft Power BI assets crawled in Atlan
- Upstream lineage to SQL warehouse assets, including table- and column-level lineage for tables, for the following supported SQL sources:
 - [Amazon Redshift](https://docs.atlan.com/llms/connectors/amazon-redshift/set-up-amazon-redshift/llms.txt)
 - [Databricks](https://docs.atlan.com/llms/connectors/databricks/set-up-databricks/llms.txt)
 - [Google BigQuery](https://docs.atlan.com/llms/connectors/google-bigquery/set-up-google-bigquery/llms.txt)
 - [Microsoft Azure Synapse Analytics](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/set-up-microsoft-azure-synapse-analytics/llms.txt)
 - [Microsoft SQL Server](https://docs.atlan.com/llms/connectors/microsoft-sql-server/set-up-microsoft-sql-server/llms.txt)
 - [MySQL](https://docs.atlan.com/llms/connectors/mysql/set-up-mysql/llms.txt)
 - [Oracle](https://docs.atlan.com/llms/connectors/oracle/set-up-oracle/llms.txt)

 :::note
 Atlan generates lineage for the following methods of Oracle connectivity:
 - connection string - for example, `<host_name>:<port>/<service_name>`
 - connect descriptor - for example, `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host_name>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))`
 - Lineage generation for TNS name connectivity is currently not supported.
 :::
 - [SAP HANA](https://docs.atlan.com/llms/connectors/sap-hana/set-up-sap-hana/llms.txt)
 - [Snowflake](https://docs.atlan.com/llms/connectors/snowflake/set-up-snowflake/llms.txt) (including Stored Procedures)

 :::note
 Stored procedures are cataloged only when the Snowflake extraction method is set to **Account Usage**, which is also required for Power BI lineage to correctly map to them. This provides a holistic view when Snowflake Stored Procedure to Power BI lineage is needed.
 :::

 - [Teradata](https://docs.atlan.com/llms/connectors/teradata/set-up-teradata/llms.txt)
 - [Salesforce](https://docs.atlan.com/llms/connectors/salesforce/set-up-salesforce/llms.txt)

This document helps you understand how Atlan generates lineage to upstream SQL sources for your Microsoft Power BI assets using a custom query parser, and the steps you can take while developing reports and dashboards in Microsoft Power BI to create seamless lineage generation.

## Lineage generation

Atlan generates lineage for your Microsoft Power BI assets as follows:

- You connect to a SQL data source such as Snowflake and extract relevant SQL tables to create a table in Microsoft Power BI for analysis.
- Once the data has been loaded, you can perform Microsoft Power BI native operations as required.
- Each table created in Microsoft Power BI and part of a dataset has a Power Query expression associated with it. For example:

 ```json
 let
 Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
 EXAMPLE_DB = Source{[Name="EXAMPLE_DATABASE_NAME",Kind="Database"]}[Data],
 EXAMPLE_Sch = EXAMPLE_DB{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
 EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
 in
 EXAMPLE_Table_Var
 ```

- Atlan retrieves the Power Query expression as a plain string from the [Microsoft Power BI API](https://learn.microsoft.com/en-us/rest/api/power-bi/admin/workspace-info-get-scan-result) response.
- Atlan's custom query parser then parses the Power Query expression to derive lineage between the upstream SQL tables and Microsoft Power BI table asset.

However, note that the Power Query expression can be modified in the Power Query Editor of the Power BI Desktop application. These modifications may involve using parameter substitutes and variable naming patterns in the Power Query expression.

These modifications may lead to inconsistent behavior in Atlan's query parser. This is because the latter is built on the standard format of a Power Query expression, without any modifications.

## Query parser

Atlan's custom query parser extracts lineage from Power BI by analyzing Power Query M expressions. This section describes the patterns that are supported and not supported for lineage extraction.

### Supported patterns

Atlan's query parser supports the following patterns for extracting lineage from Power BI.

#### Query string references

Power BI lets you store SQL queries as named expressions and reference them in table queries. Atlan resolves these references to extract the actual query string for lineage parsing.

Atlan supports the following reference patterns: `db_source`, `#"db@source"`, or `""&db_source&""`.

**Example:** Named expression reference

If your table query references `db_source`:

```
let
 Source = Value.NativeQuery(Snowflake.Databases("server","WH"){[Name="DB"]}[Data], db_source, null)
in
 Source
```

And the named expression `db_source` is defined at the dataset level:

```
let
 Source = "SELECT * FROM SOURCE"
in
 Source
```

Atlan resolves this by replacing `db_source` with `"SELECT * FROM SOURCE"`, enabling lineage extraction from the query.

#### Table source references

Power BI lets you define reusable data source connections as named expressions and reference them in table queries. This is a common pattern for sharing connections across multiple tables.

Atlan resolves these references by inlining the full source expression before parsing.

Atlan supports the following reference patterns: `Source = db_source` or `Source = #"db@source"`.

**Example:** Table source reference

If your table query references `db_source`:

```json
let
Source = db_source,
EXAMPLE_Sch = db_source{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var
```
And the named expression `db_source` is defined at the dataset level:

```json
let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
EXAMPLE_DB = Source{[Name="EXAMPLE_DATABASE_NAME",Kind="Database"]}[Data]
in
EXAMPLE_DB
```

Atlan inlines the full `db_source` expression before parsing to resolve the connection and extract lineage.

#### Table functions

For column-level lineage generation, Atlan's custom query parser supports parsing expressions with the following [Table Functions](https://learn.microsoft.com/en-us/powerquery-m/table-functions):
- [Table.RenameColumns](https://learn.microsoft.com/en-us/powerquery-m/table-renamecolumns)
- [Table.TransformColumnNames](https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumnnames)
- [Table.TransformColumns](https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumns)

### Unsupported patterns

Atlan's query parser doesn't support the following patterns. Avoid these patterns in your Power Query expressions for seamless lineage generation.

#### Using parameters

The Power Query expression associated with a table can be manually modified to serve different use cases. For example, if you're creating multiple tables using data from the same database and schema at source, you may want to use [dynamic M query parameters](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters) to substitute common values in Power Query expressions.

Avoid using the following words to define your parameter names:
- `Database`
- `Schema`
- `Table`
- `View`
- `Warehouse`
- `Role`

Avoid including any spaces in your parameter names—for example, `( Example : Example DB )`.

The following example includes `WarehouseName`, `DatabaseName`, `SchemaName`, and `TableName` as parameters, which aren't supported:

```json
let
Source = Snowflake.Databases("example.snowflakecomputing.com",WarehouseName,[Role="EXAMPLE_ROLE"]),
DatabaseName = Source{[Name=DatabaseName,Kind="Database"]}[Data],
EXAMPLE_Sch = DatabaseName{[Name=SchemaName,Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name=TableName,Kind="Table"]}[Data]
in
EXAMPLE_Table_Var
```

#### Parameter syntax

There are different formats for the syntax used in parameter names for Power Query expressions. For example, `param_name`, `#"param_name"`, or `#"param name"`.

Use plain text format for parameter names and avoid special characters—for example, `#`, `"`, and more.

The following example includes `#"DatabaseName"` as parameter name, which isn't supported:

```json
let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
DatabaseName = Source{[Name=#"DatabaseName",Kind="Database"]}[Data],
EXAMPLE_Sch = DatabaseName{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var
```

#### Variable names

While using parameters in Power Query expressions, variable names shouldn't match parameter names.

The following example uses `DatabaseName` as both a parameter name and variable name, which isn't supported:

```json
let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
DatabaseName = Source{[Name=DatabaseName,Kind="Database"]}[Data],
EXAMPLE_Sch = DatabaseName{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var
```

#### Power query functions

Upstream lineage isn't supported when the data source expression involves the use of certain built-in Power Query functions. The following functions aren't supported:

- `Csv.Document`
- `DateTime.LocalNow`
- `Excel.Workbook`
- `Folder.Files`
- `Json.Document`
- `List.Dates`
- `SharePoint.Files`
- `SharePoint.Tables`
- `UsageMetricsDataConnector.GetMetricsData`
- `Xml.Tables`

---