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

[davidestg]

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 in a table. 

But, how can I add a row in a table or change parameter both from a funtion? 

BR

David

[Greg_Deckler]

Can you provide some example inputs/outputs in terms of data. Are you trying to do this in Power Query or DAX?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[davidestg]

Sure, I'm trying in power Query into a PowerBI

I have a table that need an increase index when I have some change between three columns. I mean: 

Worker	Fecha	Valor	Index
A	16/04/2018	TRUE	1
A	16/04/2018	TRUE	1
A	16/04/2018	FALSE	2
A	16/04/2018	TRUE	3
B	15/04/2018	TRUE	4
B	16/04/2018	FALSE	5
B	16/04/2018	TRUE	6
B	16/04/2018	TRUE	6

Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution. 

Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table. 

In Excel is very easy because yo can calculate from the information in the last cell, but here I can't. 

The excel formula could be: 

=SI(A2=A1;SI(C2="FALSE";SI(C1="FALSE";E1;E1+1);SI(C1="FALSE";E1+1;E1));E1+1)

[danextian · Accepted solution]

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.

[davidestg]

I applied the solution in a table with a 14K rows and the step to List.SumRange calculate over 4GB of data during aprox. 20 minutes. Do you know how to reduce the duration? 

[davidestg]

I correct. The process takes 3 hours calculating and has not yet finished, consuming 43G in the download

[danextian]

Hi @davidestg,

I didn't realize you had that many rows. While the initial solution I gave you worked and the preview in PQ loaded fast, it wouldn't be the case when loading it to the model.

After several attempts at finding a better way in achieveing the same result in Power Query, I've given up. Even using merge, loading the data would take a very long time. The data being loaded was already more than 4 gigs when I cancelled the loading process when the dummy data was only about 1 MB. 

You would have better luck doing it in DAX than in M.

Try this PBIX:

https://drive.google.com/open?id=0B1knkMKNyu5IdDBGMjhvVlFKc2c

[davidestg]

Amazing, excellent solution. You are a genius. Thank you because I tried a lot of thinks witout results!!. 

About the other question, is possible to change the parameter value or insert row in a different table?