none
Reference table name after previous step while using Table.AddColumn to add custom columns RRS feed

  • Question

  • I am trying to automate the creation of a bunch of m code from an excel worksheet.  Here is an example of the part of my query being automated:

    #"Added Charge Complete.data_matrix_id" = Table.AddColumn(#"Added timestamp", "SystemData.pvAvgVoltage", each if ([event_name] = "system" and [event_type] = 3) then [data.data.4] else null\),
    #"Added 0.energy Delivered" = Table.AddColumn(#"Added timestamp", "1.mpptAvgPower", each if ([event_name] = "system" and [event_type] = 3) then [data.data.5] else null\),
    #"Added data_matrix_id.elapsed time" = Table.AddColumn(#"Added timestamp", "pvAvgVoltage.mpptEnergy", each if ([event_name] = "system" and [event_type] = 3) then [data.data.6] else null\),

    According to the Table.AddColumns reference, the signature is:

    Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

    The problem I'm encountering right now is that I'm hard-coding the table parameter as

    #"Added timestamp"

    I want to instead have this table paramter simply refer to the name of the previous step, such as:

    #"Added Charge Complete.data_matrix_id" = Table.AddColumn(*NAME_OF_TABLE_FROM_PREVIOUS_STEP*, "SystemData.pvAvgVoltage", each if ([event_name] = "system" and [event_type] = 3) then [data.data.4] else null\),
    #"Added 0.energy Delivered" = Table.AddColumn(*NAME_OF_TABLE_FROM_PREVIOUS_STEP*, "1.mpptAvgPower", each if ([event_name] = "system" and [event_type] = 3) then [data.data.5] else null\),
    #"Added data_matrix_id.elapsed time" = Table.AddColumn(*NAME_OF_TABLE_FROM_PREVIOUS_STEP*, "pvAvgVoltage.mpptEnergy", each if ([event_name] = "system" and [event_type] = 3) then [data.data.6] else null\),

    Is there a way to dynamically refer to the name of the table that results after the previous step?

    Tuesday, December 3, 2019 3:39 PM

Answers

  • Hi

    Before everything else and before one replies to Reference table name from the previous step could you close that duplicate please? Thanks

    The following should do what you want according to the code you posted:

    let
        PreviousStepName = ...,
        ColumnsToAdd = {"SystemData.pvAvgVoltage","1.mpptAvgPower","pvAvgVoltage.mpptEnergy"},
        AddedColumns = List.Accumulate(ColumnsToAdd, PreviousStepName,
            (state,current)=> Table.AddColumn(state, current, each
                if ([event_name] = "system" and [event_type] = 3)
                then Record.Field(_, "data.data." & Text.From(List.PositionOf(ColumnsToAdd, current) +4))
                else null
                )
        )
    in
        AddedColumns

    EDIT (forgot, sorry): Re. Is there a way to dynamically refer to the name of the table that results after the previous step? You just refer to the #"Previous Step Name" (assuming it returns a Table):

    #"Added Charge Complete.data_matrix_id" = Table.AddColumn(#"Added timestamp", "SystemData.pvAvgVoltage", each if ([event_name] = "system" and [event_type] = 3) then [data.data.4] else null\),
    #"Added 0.energy Delivered" = Table.AddColumn(#"Added Charge Complete.data_matrix_id", "1.mpptAvgPower", each if ([event_name] = "system" and [event_type] = 3) then [data.data.5] else null\),
    #"Added data_matrix_id.elapsed time" = Table.AddColumn(#"Added 0.energy Delivered", "pvAvgVoltage.mpptEnergy", each if ([event_name] = "system" and [event_type] = 3) then [data.data.6] else null\),

     



    Tuesday, December 3, 2019 5:18 PM