none
Changing Column Values RRS feed

  • Question

  • Hi,

    Power Query using M is very powerful when adding new columns, but in some cases the transformation requires to change values in an existing column.

    Is there any way to achieve this in Power Query other than creating a new column and then deleting the original column?

    If not, will it be difficult to implement such a feature?

    Thanks in advance,

    Pål

    Wednesday, October 23, 2013 10:33 PM

Answers

  • Hi Pal,

    You should be able to use Table.TransformColumns to achieve this. The function takes a table and a list of columns and functions to apply to each column. You can even define your own function as a different query in the workbook that takes a different column as parameter to control what transformation to apply to the input column.

    Thanks,
    M.

    Sunday, October 27, 2013 7:52 PM

All replies

  • Hi Pal,

    Are you looking for something basic like Replace Values? http://office.microsoft.com/en-us/excel-help/replace-values-HA104054000.aspx

    There are other ways to change column (cell) values, like Split Column, etc. Let me know if this works or if you have an specific input/output expectation and we can look at it.

    Thanks,
    M.

    Thursday, October 24, 2013 2:40 AM
  • Hi Miguel,

    In my case I need to change a columns value based on a criteria using another column value, like an UPDATE query with a WHERE clause in SQL.

    Thanks,

    Pål

    Thursday, October 24, 2013 7:25 AM
  • So essentially, what you're asking for is to be able to condense the following into a single operation

    1. Insert Custom Column
    2. Remove Columns on original column
    3. Rename custom column to match the original column name
    4. Reorder the columns to restore the original order

    so that the resulting M program would look something like this -- except without having to define the function Table.UpdateColumn manually?

    let
        Table.UpdateColumn = (table, column, fn) => let
            ColumnOrder = Table.ColumnNames(table),
            TmpName = Text.NewGuid(),
            InsertedCustom = Table.AddColumn(table, TmpName, fn),
            RemovedColumns = Table.RemoveColumns(InsertedCustom, {column}),
            RenamedColumns = Table.RenameColumns(RemovedColumns, {{TmpName, column}}),
            ReorderedColumns = Table.ReorderColumns(RenamedColumns, ColumnOrder)
        in
            ReorderedColumns,
        Source = Csv.Document(File.Contents("D:\test.csv"),null,null,null,1252),
        FirstRowAsHeader = Table.PromoteHeaders(Source),
        UpdatedColumn = Table.UpdateColumn(FirstRowAsHeader, "Name", each "Hello " & [Name] & "!")
    in
        UpdatedColumn

    Thursday, October 24, 2013 4:44 PM
  • Yes, Table.UpdateColumn(table, column, fn) is exactly what I would like to see as a standard function in the Excel Formula Library!

    And if you have the time, add an extra optional parameter for changing the column type :-)

    Keep up the good work!

    Regards,

    Pål

    Saturday, October 26, 2013 7:47 PM
  • Hi Pal,

    You should be able to use Table.TransformColumns to achieve this. The function takes a table and a list of columns and functions to apply to each column. You can even define your own function as a different query in the workbook that takes a different column as parameter to control what transformation to apply to the input column.

    Thanks,
    M.

    Sunday, October 27, 2013 7:52 PM
  • Another option is Table.ReplaceValue. Instead of passing in literal values, you can pass in a function that references other fields in the row.

    For example, if you had a table with three columns (a, b, and c), you could use the following to search c for the value in a, and replace it with b.

    = Table.ReplaceValue(Source, each [a], each [b], Replacer.ReplaceText, {"c"})

    Ehren
    • Proposed as answer by Lii Shyueyeuan Friday, January 22, 2016 11:11 AM
    Monday, October 28, 2013 6:42 PM
    Owner
  • See related post on this forum with solution here 

    Tuesday, December 16, 2014 1:15 PM