Forum Discussion

davidestg's avatar
davidestg
Icon for Helper I rankHelper I
8 years ago
Solved

How to modify a parameter from a function or add rows into a table from a function

Hi all.    I need to define an index when a three different columns have any change. As I'm not found the solutions with the index creations, I'd like to save the last index used in a parameter or ...
  • danextian's avatar
    danextian
    8 years ago

    Hi davidestg,

     

    Try this:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8vyk4tUtJRcktNzkgE0mGJOflFSrE60UqOQJ6hmb6Bib6RgaEFkBMSFOpKqoybo08wIU1OIBlTnDLYjcOQwq0JIhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
        //Record.FieldValues(_) concatenates all columns into one, _ is  used delimiter
        #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Combined Columns", each Text.Combine(Record.FieldValues(_), "_"), type text),
    
        //added Index column, needed to create relative reference
        #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1),
    
        /*compares the values in "Combined Columns" column of the prevous row from the previous step with that of the "Combined Columns" column
          of the current row from the current step,
          value for row 1 is 1,
          if the next row is the same as previous, value is 0 */
        #"Added Custom1" = Table.AddColumn(#"Added Index", "Changed?", each try (if #"Added Index"[Combined Columns]{[Index]-1} = [Combined Columns] then 0 else 1) otherwise  1, Int64.Type),
    
        //create modified index
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Modified Index", each List.Sum(List.Range(#"Added Custom1"[#"Changed?"],0,[Index]+1)), Int64.Type)
    in
        #"Added Custom2"

    But Greg_Deckler migh have  a more elegant solution.