Adding index column in ADF Power Query

[SanderTK]

Hi everyone,

At the moment I am trying to work around Spark M-code restrictions to create an index column within Data Factory's Power Query.

While the option to add an index column is present in the interface, when used it will return the error:

"Expression.Error: The Power Query Spark Runtime does not support the function Table.AddIndexColumn."

I am now trying to use a Power Query formula in "add custom column" to create an index column based on row count.

It is succesful in creating a table with one index column but it is then impossible to join/merge that table to the table which is in need of an index column (there is no join condition since the table contains only the one column).

It is also possible to create an index column as a "Table.AddColumn" but this gives several errors and results in an indexing of every record in that table for the amount of records in that table (so with 7 rows, it will create 7 rows for every one of those rows and gives them an index from 1 to 7).

I feel like I'm getting close to a viable work-around but I am missing something to implement it succesfully.

Any and all help will be much appreciated!

[miguel · Accepted solution]

Hi and welcome to the Fabric Data Factory community forum. This forum is specific to the Fabric Data Factory experience.

For questions related to Azure Data Factory you can use the Azure Data Factort community forum using the link below:

https://techcommunity.microsoft.com/t5/azure-data-factory/bd-p/AzureDataFactory

There are limitations on the integration that Power Query has in ADF.

Power Query activity in Azure Data Factory - Azure Data Factory | Microsoft Learn

[frithjof_v]

Heavily inspired by Re: Adding index column in ADF Power Query - Microsoft Fabric Community:

let
    MyTable = Table.FromRecords({
        [Name="Alice", Age="30", Country="USA"],
        [Name="Bob", Age=null, Country="UK"],
        [Name="Charlie", Age="35", Country=null]
    },
    
    type table[Name = Text.Type, Age = Text.Type, Country = Text.Type]
    ),
    IndexList = List.Numbers(1, Table.RowCount(MyTable)),
    TableFromColumns = Table.FromColumns({MyTable[Name], MyTable[Age], MyTable[Country], IndexList}, type table[Name = Text.Type, Age = Int64.Type, Country = Text.Type, Index = Int64.Type])
in
    TableFromColumns

or

let
    MyTable = Table.FromRecords({
        [Name="Alice", Age="30", Country="USA"],
        [Name="Bob", Age=null, Country="UK"],
        [Name="Charlie", Age="35", Country=null]
    }),
    IndexList = List.Numbers(1, Table.RowCount(MyTable)),
    TableFromColumns = Table.FromColumns(Table.ToColumns(MyTable) & {IndexList}, Table.ColumnNames(MyTable) & {"Index"})
in
    TableFromColumns

(this second alternative means you don't need to specify the selected column names from MyTable inside the TableFromColumns. Instead, you select the whole table.)

Heavily inspired by Power Query - Combine tables without Merging 🔗🧲 (youtube.com):

(Also with this alternative, you don't need to specify the column names when combining the two tables.)

let
    MyTable = Table.FromRecords({
        [Name="Alice", Age="30", Country="USA"],
        [Name="Bob", Age=null, Country="UK"],
        [Name="Charlie", Age="35", Country=null]
    }
    ),
    IndexList = List.Numbers(1, Table.RowCount(MyTable)),
    IndexListAsTable = Table.FromList(IndexList, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error),
    TableToColumns = Table.PromoteHeaders(Table.FromColumns(Table.ToColumns(Table.DemoteHeaders(MyTable)) & Table.ToColumns(Table.DemoteHeaders(IndexListAsTable))))
in
    TableToColumns

Heavily inspired by Solved: Re: Merge tables without a join, placing new colum... - Microsoft Fabric Community:

(This uses quite different functions from the previous examples.
Also, you don't need to specify the column names when combining the two tables.)

let
    MyTable = Table.FromRecords({
        [Name="Alice", Age="30", Country="USA"],
        [Name="Bob", Age=null, Country="UK"],
        [Name="Charlie", Age="35", Country=null]
    }
    ),
    MyTableTransposed = Table.Transpose(Table.DemoteHeaders(MyTable)),
    IndexList = List.Numbers(1, Table.RowCount(MyTable)),
    IndexListAsTable = Table.FromList(IndexList, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error),
    IndexTransposed = Table.Transpose(Table.DemoteHeaders(IndexListAsTable)),
    AppendTables = Table.Combine({MyTableTransposed, IndexTransposed}),
    TransposeAppendedTables = Table.Transpose(AppendTables),
    #"Promoted Headers" = Table.PromoteHeaders(TransposeAppendedTables, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Age", Int64.Type}, {"Country", type text}, {"Index", Int64.Type}})
in
    #"Changed Type"

These seem like interesting ways to just add a column to a table in Power Query without using a join condition.

I haven't checked if any of these will work in ADF Power Query, though.

[frithjof_v]

I read somewhere someone mention to transform the table into a list (perhaps this function https://learn.microsoft.com/en-us/powerquery-m/table-tolist), and then merge it with the list of numbers you have made (perhaps this function https://learn.microsoft.com/en-us/powerquery-m/list-zip).

And after that convert the resulting combined list into a table again.

I also think you can get great help with Power Query over at the Microsoft Power BI forum (this is the Microsoft Fabric Dataflows Gen2 forum).

https://community.fabric.microsoft.com/t5/Power-Query/bd-p/power-bi-services

[SanderTK]

Apologies, completely forgot that I was still browsing the Fabric community.

I will have a look at the functions to see if I can make something work.

Thank you for the response!

[frithjof_v]

This seems to work somehow (in Power BI Desktop, I haven't tried it in ADF Power Query):

let
    MyTable = Table.FromRecords({
        [Name="Alice", Age="30", Country="USA"],
        [Name="Bob", Age=null, Country="UK"],
        [Name="Charlie", Age="35", Country=null]
    },
    
    type table[Name = Text.Type, Age = Text.Type, Country = Text.Type]
    ),
    MyTableList = Table.ToList(MyTable),
    IndexList = List.Numbers(1, Table.RowCount(MyTable)),
    ZippedList = List.Zip({MyTableList, IndexList}),
    #"Converted to Table" = Table.FromList(ZippedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}, {"Column1.3", type text}, {"Column1.4", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Name"}, {"Column1.2", "Age"}, {"Column1.3", "Country"}, {"Column1.4", "Index"}})
in
    #"Renamed Columns"

Perhaps there are some other functions which will be more efficient.

But it seems to do the job (at least in Power BI Desktop).

[frithjof_v]

Another alternative, perhaps a bit more "clean" (?) because the List.Zip here works with records instead of strings:

let
    MyTable = Table.FromRecords({
        [Name="Alice", Age="30", Country="USA"],
        [Name="Bob", Age=null, Country="UK"],
        [Name="Charlie", Age="35", Country=null]
    },
    
    type table[Name = Text.Type, Age = Text.Type, Country = Text.Type]
    ),
    MyTableAsListOfRecords = Table.ToRecords(MyTable),
    IndexList = List.Numbers(1, Table.RowCount(MyTable)),
    IndexTable = Table.FromList(IndexList, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error),
    IndexAsListOfRecords = Table.ToRecords(IndexTable),
    CombinedList = List.Zip({MyTableAsListOfRecords, IndexAsListOfRecords}),
    CombinedListAsTable = Table.FromList(CombinedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExtractMyTable = Table.AddColumn(CombinedListAsTable, "Orig_Table", each [Column1]{0}),
    ExtractIndex = Table.AddColumn(ExtractMyTable, "Index", each [Column1]{1}),
    ExpandMyTable = Table.ExpandRecordColumn(ExtractIndex, "Orig_Table", {"Name", "Age", "Country"}, {"Name", "Age", "Country"}),
    ExpandIndex = Table.ExpandRecordColumn(ExpandMyTable, "Index", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(ExpandIndex,{"Column1"})
in
    #"Removed Columns"

[SanderTK]

Both Table.ToList and Table.ToRecords are sadly not allowed in ADF Power Query.

They both result in the error:
"UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out."
I suppose when multiple agents work on the data creating lists and records dynamic in shape/size does not work

[frithjof_v]

Is this the feature you are using? ("Data Wrangler")

There seems to be a list of supported functions here:

https://learn.microsoft.com/en-us/azure/data-factory/wrangling-overview

https://learn.microsoft.com/en-us/azure/data-factory/wrangling-functions

Sadly, I don't have experience with ADF.

Probably the M language functionality is a bit limited because it's running on Spark (multiple nodes) as you mention.

I guess perhaps this thread is also talking about the same feature, it seems Data Wrangler got renamed to just ADF Power Query: https://stackoverflow.com/questions/70792385/what-are-the-differences-between-adf-power-query-and-po...