none
How do I assign a value to the first row of a column RRS feed

  • Question

  • Hi,

    How do I assign a value to the first row of a column?

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Friday, May 12, 2017 8:23 PM

Answers

  • Even simpler:

    (table as table, columnName as text, value as any) as table  =>
    let
        replacedRecordValue = Record.TransformFields(table{0}, {columnName, each value}),
        replacedFirstRow = Table.ReplaceRows(table, 0, 1, {replacedRecordValue})
    in
        replacedFirstRow

    Saturday, May 13, 2017 9:16 PM
  • Good question.

    The following function keeps the first row of a Table, replaces the current value in column Column with Value, and appends the original table without the first row.

    (Table as table, Column as text, Value as any) as table =>
    let
        KeptFirstRow = Table.FirstN(Table,1),
        ReplacedValue = Table.ReplaceValue(KeptFirstRow,Table.Column(KeptFirstRow,Column){0},Value,Replacer.ReplaceValue,{Column}),
        Result = ReplacedValue & Table.Skip(Table,1)
    in
        Result

    Saturday, May 13, 2017 6:54 AM
  • Another approach would be to convert the first row of the table to a record, assign your value to the appropriate record field, and then replace the first row in the table with the modified record.

    As a custom function:

    (table as table, columnName as text, value as any) as table => let
    firstRowRecord = Table.SingleRow(Table.FirstN(table,1)),
    replacedRecordValue = Record.TransformFields(firstRowRecord, {columnName, each value}),
    replacedFirstRow = Table.ReplaceRows(table, 0, 1, {replacedRecordValue})
    in
    replacedFirstRow

    Saturday, May 13, 2017 4:40 PM

All replies

  • Good question.

    The following function keeps the first row of a Table, replaces the current value in column Column with Value, and appends the original table without the first row.

    (Table as table, Column as text, Value as any) as table =>
    let
        KeptFirstRow = Table.FirstN(Table,1),
        ReplacedValue = Table.ReplaceValue(KeptFirstRow,Table.Column(KeptFirstRow,Column){0},Value,Replacer.ReplaceValue,{Column}),
        Result = ReplacedValue & Table.Skip(Table,1)
    in
        Result

    Saturday, May 13, 2017 6:54 AM
  • Another approach would be to convert the first row of the table to a record, assign your value to the appropriate record field, and then replace the first row in the table with the modified record.

    As a custom function:

    (table as table, columnName as text, value as any) as table => let
    firstRowRecord = Table.SingleRow(Table.FirstN(table,1)),
    replacedRecordValue = Record.TransformFields(firstRowRecord, {columnName, each value}),
    replacedFirstRow = Table.ReplaceRows(table, 0, 1, {replacedRecordValue})
    in
    replacedFirstRow

    Saturday, May 13, 2017 4:40 PM
  • Superb Marcel!

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Saturday, May 13, 2017 5:31 PM
  • Even simpler:

    (table as table, columnName as text, value as any) as table  =>
    let
        replacedRecordValue = Record.TransformFields(table{0}, {columnName, each value}),
        replacedFirstRow = Table.ReplaceRows(table, 0, 1, {replacedRecordValue})
    in
        replacedFirstRow

    Saturday, May 13, 2017 9:16 PM