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
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
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!
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).
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"
