Supported Power Query M functions

Power Query is a data transformation and preparation engine. It uses a scripting language called Power Query M formula language—also known as M—for all transformations.

M is considered a "mashup" language. The Power Query engine filters and combines data from supported data sources. The "mashed up" data is then expressed using M. M is used by Power BI. It is not relevant to other integrations in Collibra.

The Collibra Data Lineage service performs lexical and syntax analysis of M. With regard to syntax analysis, the Collibra Data Lineage service instances currently support the following functions.

Note Not all functions have an impact on the technical lineage, so even though an error is raised for any unsupported functions, it might not mean that your lineage is incomplete. We are working to support the most common Power Query functions. If you have a Power Query function that is not yet supported and it’s very important to you, please create an Ideation ticket.

For complete information on these functions, see the Microsoft documentation on accessing data functions.

    • AmazonRedshift.Database
    • AnalysisServices.Database
      Note  
      • This function is fully supported if no MDX queries are used.
      • If MDX queries are used and they resemble SQL, they will be parsed by the SQL parser.
      • We don't currently support this function if used with MDX queries that resemble DAX, as the Collibra Data Lineage service instances can't parse such queries.
    • AnalysisServices.Databases
    • Csv.Document
    • Databricks.Contents
    • Databricks.Catalogs
    • Excel.Workbook
    • File.Contents
    • GoogleBigQuery.Database
    • Odbc.DataSource
    • Odbc.Query
    • Oracle.Database
    • PostgreSQL.Database
    • SapBusinessWarehouse.cubes
      Note Lineage can't be generated if you use the Table.SelectRows function in conjunction with the SapBusinessWarehouse.cubes function, because the Table.SelectRows function requires a table parameter, not a source parameter.
    • SapHana.Database
    • Snowflake.Database
    • Sql.Database
    • Sql.Databases
    • Sybase.Database
    • Teradata.Database
    • Web.Contents
    • Cube.AddAndExpandDimensionColumn
    • Cube.Transform
    • PowerBI.Dataflows
      Note We only support dataflows without parameters that contain the following information:
      • workspace ID
      • dataflow ID
      • entity (Power BI Table) ID
    • PowerPlatform.Dataflows
      Note We only support dataflows without parameters that contain the following information:
      • workspace ID
      • dataflow ID
      • entity (Power BI Table) ID
    • Replacer.ReplaceText
    • Replacer.ReplaceValue
    • Table.AddColumn
    • Table.AddIndexColumn
    • Table.Combine

      If Collibra Data Lineage can’t determine the column names in a source file or database, but the PowerBI column names are known and there is only one source file or database, then corresponding database/file columns are created and technical lineage is preserved. However, if column names can’t be determined and there are multiple source files or databases, as is the case when the Table.Combine function is used, then it’s not possible to know which source column corresponds to the Power BI column. This results in an error and the technical lineage is broken.

      To resolve this issue, a dummy column with the value “*” is created in the source table and the Power BI table:

      This preserves the technical lineage at the table level:

    • Table.CombineColumns
    • Table.DuplicateColumn
    • Table.ExpandTableColumn
    • Table.FromList
    • Table.FromRecords
    • Table.FromRows
    • Table.NestedJoin
    • Table.PromoteHeaders
    • Table.RemoveColumns
    • Table.RenameColumns
    • Table.ReorderColumns
    • Table.ReplaceValue
    • Table.SelectColumns
    • Table.SplitColumn
    • Table.Unpivot
    • Table.UnpivotOtherColumns
    • Table.TransformColumnNames
      Note Only the following parameters are supported: Text.Upper, Text.Lower, and Text.Proper.
      Note If columns in Power BI are renamed before they are transformed via this function (Text.Upper, Text.Lower or Text.Proper), the original column names are now shown in the database node in the technical lineage.
    • Value.NativeQuery
      Note Query parameters are supported. Core parameters are not supported.
    • Table.AddKey
    • Table.AlternateRows
    • Table.Buffer
    • Table.Distinct
    • Table.ExpandListColumn
    • Table.FillDown
    • Table.FillUp
    • Table.FindText
    • Table.FirstN
    • Table.InsertRows
    • Table.IsEmpty
    • Table.LastN
    • Table.MaxN
    • Table.MinN
    • Table.Range
    • Table.RemoveFirstN
    • Table.RemoveLastN
    • Table.RemoveMatchingRows
    • Table.RemoveRows
    • Table.RemoveRowsWithErrors
    • Table.Repeat
    • Table.ReplaceErrorValues
    • Table.ReplaceKeys
    • Table.ReplaceMatchingRows
    • Table.ReplaceRows
    • Table.ReverseRows
    • Table.SelectRows
    • Table.SelectRowsWithErrors
    • Table.Skip
    • Table.Sort
    • Table.TransformColumns
    • TableTransformColumnTypes
    • Table.First
    • Table.Last
    • Table.Max
    • Table.Min
    • Table.SingleRow

  • Note Using unsupported transformations can cause parsing errors.

    • Table.FromRecords
    • SharePoint.Tables
    • Folder.Files
    • PowerBIRESTAPI.Navigation
    • DB2.Database
    • Table.ExpandRecordColumn
    • Table.Group