none
Add Columns using a function populated from a list RRS feed

  • Question

  • Is it possible to create a function that adds a number of columns to a table based on the items in a list.  For example I would like the M code to be able to reference a table that has a text list of column names, then create a function to read the list and repeatedly add the columns an AddColumns step for each value in the list. 

    Where AccountField and GeoField are values in the list. 
        AddAccount = Table.AddColumn(Source, "AccountField", each ""),   --- as step1
        AddGeo = Table.AddColumn(AddAccount , "GeoField", each ""),   --- as step2

    The purpose is to add these fields to a source table and then update the values using a mapping table maintained by the users e.g. Account Source 'AAAAA' maps to Account Result '11111'.

    Thnx

    Monday, March 9, 2015 11:17 AM

Answers

  • One way of doing this is with List.Generate:

    let
    // Original table and added columns Source = Table.FromColumns({{"One", "Two", "Three"}, {1, 2, 3}}), ColumnList = {"AccountField", "GeoField"},

    // Construct new table AddedColumns = List.Generate( () => [Table=Source, Columns=ColumnList], (state) => state[Columns] <> null, (state) => [ Table=Table.AddColumn(state[Table], List.First(state[Columns]), each null), Columns=if List.Count(state[Columns]) = 0 then null else List.Skip(state[Columns], 1)], (state) => state[Table]), Result = List.Last(AddedColumns) in Result

    Another approach is to decompose the table into columns and then reassemble from the original columns plus the added columns:

    let
        // Original table and added columns
        Source = Table.FromColumns({{"One", "Two", "Three"}, {1, 2, 3}}),
        ColumnList = {"AccountField", "GeoField"},
    
        // Construct new table
        EmptyColumn = List.Repeat({null}, Table.RowCount(Source)),
        EmptyColumns = List.Repeat({EmptyColumn}, List.Count(ColumnList)),
        NewTable = Table.FromColumns(
            Table.ToColumns(Source) & EmptyColumns,
            Table.ColumnNames(Source) & ColumnList)
    in
        NewTable
    Which approach is better will depend in part on how the values in the added columns will actually be constructed.


    • Edited by Curt Hagenlocher Monday, March 9, 2015 1:23 PM
    • Proposed as answer by Curt Hagenlocher Monday, March 9, 2015 1:23 PM
    • Marked as answer by PQUK Monday, March 9, 2015 6:37 PM
    Monday, March 9, 2015 1:22 PM

All replies

  • One way of doing this is with List.Generate:

    let
    // Original table and added columns Source = Table.FromColumns({{"One", "Two", "Three"}, {1, 2, 3}}), ColumnList = {"AccountField", "GeoField"},

    // Construct new table AddedColumns = List.Generate( () => [Table=Source, Columns=ColumnList], (state) => state[Columns] <> null, (state) => [ Table=Table.AddColumn(state[Table], List.First(state[Columns]), each null), Columns=if List.Count(state[Columns]) = 0 then null else List.Skip(state[Columns], 1)], (state) => state[Table]), Result = List.Last(AddedColumns) in Result

    Another approach is to decompose the table into columns and then reassemble from the original columns plus the added columns:

    let
        // Original table and added columns
        Source = Table.FromColumns({{"One", "Two", "Three"}, {1, 2, 3}}),
        ColumnList = {"AccountField", "GeoField"},
    
        // Construct new table
        EmptyColumn = List.Repeat({null}, Table.RowCount(Source)),
        EmptyColumns = List.Repeat({EmptyColumn}, List.Count(ColumnList)),
        NewTable = Table.FromColumns(
            Table.ToColumns(Source) & EmptyColumns,
            Table.ColumnNames(Source) & ColumnList)
    in
        NewTable
    Which approach is better will depend in part on how the values in the added columns will actually be constructed.


    • Edited by Curt Hagenlocher Monday, March 9, 2015 1:23 PM
    • Proposed as answer by Curt Hagenlocher Monday, March 9, 2015 1:23 PM
    • Marked as answer by PQUK Monday, March 9, 2015 6:37 PM
    Monday, March 9, 2015 1:22 PM
  • Thank you Curt, this works exactly as requested.
    Monday, March 9, 2015 6:38 PM