Forum Discussion
Solved
Removing cumulative frequency using power(m) query
Hi! I have the below table where the data gets generated every 1-2 seconds. Column A - Datetime Column B - Cumulative Counter Column C - Calculated in Excel to remove cumulative frequency IF(B2...
I was able to use the below query for a dax calculated column which gave me the desired results without taking a long time to process the large dataset. Had to create an index column first in power query.
Column = var e = CALCULATE(MAX(Query4[B]),FILTER(Query4,Query4[Index]=EARLIER(Query4[Index])-1))var f = Query4[B]return IF(f=0,0,IF(f>=e,f-e,e))
Hi pwrbiadm
This might run faster in DAX. However, place the following M code in a blank query to see the steps. If it's not fast enough we'll try another approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDsAgCAWvYlibFlC0ehXj/a/RNl01fZiUsCIzhM8YJLzrnUF7ThQpOGEbM834FvJfwRZCRULxBIH9Dw+vjPDm4Q0NY4xx0YxocWgTROtFf4qrh8Au7kMkIdy9bnn2nyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type number}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"B"}),
col_ = List.RemoveLastN({0} & #"Replaced Value1"[B], 1),
res_ = Table.FromColumns(Table.ToColumns(#"Replaced Value1") & {col_}, {"A", "B","B_2"}),
#"Changed Type1" = Table.TransformColumnTypes(res_,{{"B_2", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [B]-[B_2], type number),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom",null,0,Replacer.ReplaceValue,{"B_2"})
in
#"Replaced Value"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers