locked
List.Accumulate try otherwise error- not being handled RRS feed

  • Question

  • Hi 

    I wrote a function for power query to standardize a table to a control list. There is an error being generated if I delete a column in the original table that try otherwise is not catching.

    The function requires a table to convert, and a list to convert to. List.Accumulate will try to add a column for each item in the list and if an error is thrown, use the state instead and then loop to the next item.

    I then keep only columns in the list, and then reorder the columns to the order in the list.

    Problem is, if I delete a column in the table to convert, try otherwise is unable to handle the error thrown and I get a message "Column C already exists". 

    The function:

    let
        Source = qryA,
        // create a parameter to hold the control list to transform the table Source TO.
        JournalColumns = qryControl[Column],
        // create a parameter to hold the count of the listed items
        JournalColumnCount = List.Count(JournalColumns),
        // List.Accumulate is a loop function that can be called within M-code
        // This List.Accumulate will loop through each item on the list and try
        // to add a column for each line to the target table.
        // A try...otherwise is added so that if the attempt to create a column
        // results in an error, the loop returns to the last state of the table and
        // continues until the count of items is reached.
        #"Add Journal Columns" =
            List.Accumulate({1..JournalColumnCount},Source, (state, current) =>
                try Table.AddColumn(state, JournalColumns{current}, each null) otherwise state),
        // Keep only columns in the new table that are on the list of items from the control table.
        #"Remove extra columns" = Table.SelectColumns(#"Add Journal Columns", JournalColumns),
        // Reorder columns in the new table to match the control table order
        #"Reorder columns" = Table.ReorderColumns(#"Remove extra columns", JournalColumns)
    in
        #"Reorder columns"

    Sunday, March 3, 2019 8:56 PM

Answers

  • Hi Micheal,

    Slightly corrected version of your code should work:

    #"Add Journal Columns" = List.Accumulate({1..JournalColumnCount},Source, (state, current) =>
    if List.Contains(Table.ColumnNames(Source), JournalColumns{current}) then state
    else Table.AddColumn(state, JournalColumns{current}, each null))



    Monday, March 4, 2019 1:46 PM

All replies

  • Hi Micheal,

    Slightly corrected version of your code should work:

    #"Add Journal Columns" = List.Accumulate({1..JournalColumnCount},Source, (state, current) =>
    if List.Contains(Table.ColumnNames(Source), JournalColumns{current}) then state
    else Table.AddColumn(state, JournalColumns{current}, each null))



    Monday, March 4, 2019 1:46 PM
  • Was about to suggest using an if statement also.:)
    Monday, March 4, 2019 3:39 PM