Forum Discussion
How to modify a parameter from a function or add rows into a table from a function
- 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.
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.
- davidestg8 years ago
Helper I
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?
- davidestg8 years ago
Helper I
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?
- davidestg8 years ago
Helper I
I correct. The process takes 3 hours calculating and has not yet finished, consuming 43G in the download
- danextian8 years ago
Super User
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