Skip to main content

What lineage does Atlan extract from Microsoft Power BI?

Atlan currently supports the following lineage for 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:

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:

    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 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:

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:

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:

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 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:

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:

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:

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