locked
Can I Modify a Power Query Table by Directly Changing One of Its Values? RRS feed

  • Question

  • Is there a way to modify a Power Query table by directly changing the value at Table1{0}[Column1] (e.g., concatenating "sauce" to "Apple")? ... 

    let
        Table1 = Table.FromRows( {{"Apple"}}, {"Column1"} )
    in
        Table1

    ... or, would I have to add a new column with the desired change, and then remove the "old" column?

    Thursday, May 14, 2015 5:47 PM

Answers

  • Ah, okay. No, there's no function that replaces values in just a single row of the table. If you've pulled it off as a record, then you can "edit" the record by concatenating it with another record using the "&" operator.

    I haven't decided yet how horrified I am at the prospect of using a table to emulate a stack...

    • Proposed as answer by Michael Amadi Tuesday, May 19, 2015 11:50 AM
    • Marked as answer by Mark Weisman Wednesday, May 20, 2015 4:24 PM
    Monday, May 18, 2015 8:42 PM

All replies

  • Table.TransformColumns would do it, as long as you wanted to change all the values in that column:

    =Table.TransformColumns(Source, {"Column1", each _ & "sauce"})

    There should be no fundamental difference between this approach and the Add+Remove approach, other than greater conciseness and the inability to reference other columns in the source row.

    Monday, May 18, 2015 4:03 PM
  • No, I don't want to concatenate a single value for the entire column.  I'm now looking at using Table.InsertRows.  Btw, I'm using Power Query tables to emulate stacks -- so, I'm peeking, popping and pushing row 1 ( i.e., value {0} ).


    Monday, May 18, 2015 6:12 PM
  • Ah, okay. No, there's no function that replaces values in just a single row of the table. If you've pulled it off as a record, then you can "edit" the record by concatenating it with another record using the "&" operator.

    I haven't decided yet how horrified I am at the prospect of using a table to emulate a stack...

    • Proposed as answer by Michael Amadi Tuesday, May 19, 2015 11:50 AM
    • Marked as answer by Mark Weisman Wednesday, May 20, 2015 4:24 PM
    Monday, May 18, 2015 8:42 PM
  • Well, if it de-horrifies you :) ... this is a learning exercise that has already yielded insights.

    At the risk of further horrifying you :) ...

    I'm creating a recursive function (Col2toCSV) that accepts a 2-column table and returns same; however, the 2nd column will be the concatenation of comma-delimited values associated with each unique value in the 1st column.

    The Col2toCSV also has its own locally declared function calls for stack operations [ e.g., Pop(t1) which employs Table.Skip( t1, 1 ) ].  And, Col2toCSV has 2 arguments of type table -- the 2nd is optional.  When the 2nd argument is null I intialize.  The recursive call is ... @Col2toCSV(Pop(t1), Push(t2, newRow)) .  Recursion terminates when t1 is empty, and t2 is returned.




    Monday, May 18, 2015 9:00 PM
  • Kurtz (Marlon Brando): "The horror... the horror..." -- Apocalypse Now (1979) 

    Here's the UDF that I wrote as a learning exercise.  Any critiques?

    // Given a single 2-column table, this UDF returns a table with the 2nd column as a comma-delimited list 
    // of text values for each unique value in the 1st column.

    ( table1 as table, optional table2 as table ) as table => 
    let
        // Initialize stacks (i.e., tables) 
        t1 = if table2 is null then 
        let 
                     // Recreate table1 w/ {"Column1", "Column2"} as headers 
                     ChgHdrs = Table.FromRows(Table.ToRows(table1)), 
                     ChgType = Table.TransformColumnTypes(ChgHdrs, {{"Column2", type text}}), 
                     SortTbl = Table.Sort(ChgType, {{"Column1", Order.Ascending}, {"Column2", Order.Ascending}}) 
                 in SortTbl 
             else table1, 
        t2 = if table2 is null then #table(type table [Column1=any, Column2=text], {}) else table2, 

        // Return a stack (i.e., table) after it has been popped 
        Pop = (table1 as table) as table => 
            let 
                Return = Table.Skip(table1, 1) 
            in  Return, 

        // Return a stack (i.e., table) after pushing a new row 
        Push = (table1 as table, newRow) as table => 
            let 
                ChkTbl = if Text.Contains(newRow{0}[Column2], ",") then Pop(table1) else table1, 
                Return = Table.InsertRows(ChkTbl, 0, newRow) 
            in Return, 

        newRow = if Table.IsEmpty(t2) or t1{0}[Column1] <> t2{0}[Column1] 
           then {[Column1=t1{0}[Column1], Column2=t1{0}[Column2]]} 
           else {[Column1=t1{0}[Column1], Column2=t2{0}[Column2]&","&t1{0}[Column2]]}, 
        Return = if Table.IsEmpty(t1) then 
                     Table.Sort(t2, {"Column1", Order.Ascending}) 
                 else @Col2toCSV(Pop(t1), Push(t2, newRow))
    in  Return

    Wednesday, May 20, 2015 4:22 PM
  • Hello and thanks for your response. I spent so much time to looking for this function, and I haven't understood why all the writtings avoid this topic. It is weird that you can create, load, transpose, read,  tables but cannot easly replace one of its unique value, something like : TableEdit (TableName {x}[y], "value to insert"}. 
    Monday, October 17, 2016 7:29 AM