Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create Columns with given name if it doesn't exist.

Hi! I have some tables with different number of column with prefix "Level" Example:  Table1 - Level1, Level2, Level3 Table2 - Level1, Level2, Level3, Level4   I want make sure all tables have Le...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

     

    What are the values in those newly created columns? Here is one sample to have the same value with the column name

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSwUbFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [level1 = _t, level2 = _t, level3 = _t, level4 = _t]),
        columnList = {"level1","level2","level3","level4","level5","level6","level7","level8","level9","level10"},
        fxAddColumn = (T as table, N as number) =>
             [
                 newColumns=List.Difference(columnList, Table.ColumnNames(Source)),
                 counter = List.Count(newColumns),
                 columnName = newColumns{N},
                 tempTable = Table.AddColumn(T, columnName, each columnName),
                 result = if N>= counter-1 then tempTable else @fxAddColumn(tempTable,N+1) 
             ][result], 
    
        Custom = fxAddColumn(Source,0)
    
    in
        Custom