Forum Discussion

SanderTK's avatar
SanderTK
Advocate II
2 years ago
Solved

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
frithjof_v's avatar
frithjof_v
Community Champion
2 years ago

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's avatar
frithjof_v
Community Champion
2 years ago

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"