Find Cumulative sum if a value is matched in colmn

[Anonymous]

Hi All,

I am working on pateint data to plot new courses. I imported PatientID and DateDiffs column using sql.

Then created custom column called courses. 

As I do not know how to calculate cumulative sum for group if value is matched in column I used following python function in power query to calculate New_Course column - dataset['New_Course']=(dataset.Courses.eq('New')).cumsum()

Is there any power query function for this python code?

Thanks in advance

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKxUNJRMtQzBJJ+qeVKsTowMSNjDCEDIHatyCwuycxLhwpbYtENFsOhdFR4VJiewmSkylgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PatientID = _t, DateDiffs = _t, Courses = _t]),
    ListOfCourses = List.Buffer(Source[Courses]),
    ListCount = List.Count(ListOfCourses),
    GenListOfRunningTotal = List.Generate(()=>[x=1,i=1], each [i]<=ListCount, each [i=[i]+1, x=if ListOfCourses{[i]}="New" then [x]+1 else [x]], each [x]),
    Result = Table.FromColumns(Table.ToColumns(Source) & {GenListOfRunningTotal},Table.ColumnNames(Source)&{"New_course"})
in
    Result

[Anonymous]

@Vijay_A_Verma 

can you please explain the logic in the step of GenListOfRunningTotal?

[Vijay_A_Verma]

Read here for List.Generate - https://exceed.hr/blog/list-generate-and-looping-in-powerquery/ 

[Anonymous]

@Vijay_A_Verma this works perfectly and does not even cause delays.

Thank You!!!

[edhans]

You need to do this in DAX. Power Query is horrible at running totals. For a few hundred records it works, and for maybe 1-2 thousand, it will work. But if you have over 10K records it will take forever, and over 100K it won't finish.

You can look at this article for running totals in DAX, or post data we can use and we can help.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[AlexisOlson]

@edhans If you write the same calculation logic as in DAX, then M is definitely worse. However, with the recursive functions List.Accumulate and List.Generate, it's possible to write running totals in O(N) rather than O(N^2) since M can handle recursion whereas DAX cannot.