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