Forum Discussion
Adding index column in ADF Power Query
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
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"
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
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-power-bi-power-query-when