Forum Discussion

SanderTK's avatar
SanderTK
Advocate II
2 years ago

Adding index column in ADF Power Query

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...
Need Help
p45cal's avatar
p45cal
Solution Supplier
2 years ago

Starting with a table (Table1) with a single column headed stuff:

let 
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddedIndex = Table.FromColumns({Source[stuff],{1..Table.RowCount(Source)}},{"stuff","Index"})
in 
AddedIndex

 

  • p45cal's avatar
    p45cal
    Solution Supplier
    2 years ago

    Further, if you have more than 1 column in your table you can try:

     

     

    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddIndex=Table.FromColumns(Table.ToColumns(Source) & {{1..Table.RowCount(Source)}}, Table.ColumnNames(Source) & {"Index"})
in
AddIndex

     

     

     

    • SanderTK's avatar
      SanderTK
      Advocate II
      2 years ago

      The technique behind this works perfectly, however, ADF still gives an error about the dataset being dynamic in length which it cannot handle. I currently can't get the exact wording of the error because I get an internal server error when trying to load datasets into ADF power query.