Below is a possible solution to the Running Total – Power Query Challenge 8.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Salary", type text}}, "en-US"), {{"Salary", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Salary"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Salary", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each _, type table [Name=text, Salary=nullable number]}}),
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Running Total", each fxRunningTotal([Count])),
#"Expanded Running Total" = Table.ExpandTableColumn(#"Invoked Custom Function", "Running Total", {"Custom"}, {"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Running Total",{"Count"})
in
#"Removed Columns"
And code for Function
(fx)=>
let
#"Added Index" = Table.AddIndexColumn(fx, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Salary],[Index]))),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Name"}, {{"Count", each _, type table [Name=text, Salary=nullable number, Index=number, Custom=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Custom]),
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Name", "Count"})
in
#"Removed Columns"
The solution file can be downloaded from PQ_Challenge_8_Solution M
Hi there very nice web site!! Guy .. Excellent .. Amazing .. I will bookmark your site and take the feeds also…I am glad to seek out numerous helpful information here within the post, we want work out more strategies on this regard, thanks for sharing. . . . . .
Thank you for the answer. I'm still new at PQ. Can you tell me how to insert the function in the code please?