Forum Discussion
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 in a table.
But, how can I add a row in a table or change parameter both from a funtion?
BR
David
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.
7 Replies
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
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)
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.
