-CAN YOU SOLVE THIS - POWER QUERY CHALLENGE 75-
(Solution in any language also welcome for Power Query Challenges)
Groups are separated by blank rows in Problem table.
You need to generate Result table where Quantity and Amount are sum of the groups.
Download Practice File - https://lnkd.in/daYnph5s
(Post answers in Comment. Your approach need not be different from others as long as you have worked out your approach independently)
#powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #excel, #excelchallenge, #python, #r
LinkedIn Post:
Generating the Result table w/ #powerquery. #bitanbit #powerbi
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
AddGroup = Table.FromRows([List.Select](http://list.select/)(List.Accumulate(Table.ToRows(Source), {}, (s, c) =>
let
i = if s = {} then 0 else List.Max(List.Transform(s, each _{0}))
in
s & {{if c{0} is null then null else if List.Last(s){0}? = null then i + 1 else i} & c}
), each _{0} <> null), {"Group"} & Table.ColumnNames(Source) ),
Solution = [Table.Group](http://table.group/)(Table.TransformColumns(AddGroup, {{"Group", each "Group " & Text.From(_)}}), {"Group"}, {{"Quantity", each List.Sum([Quantity])}, {"Amount", each List.Sum([Amount])}})
in
Solution
Not very elegant, but it works:
=LET(a,IF(A2:A20="",";",A2:A20),
b,BYROW(a,LAMBDA(a,CONCAT(a,","))),
c,IFERROR(VALUE(IFNA(TEXTSPLIT(CONCAT(b),",",";"),0)),0),
d,BYROW(c,LAMBDA(c,SUM(c))),
e,IF(B2:B20="",";",B2:B20),
f,BYROW(e,LAMBDA(e,CONCAT(e,","))),
g,IFERROR(VALUE(IFNA(TEXTSPLIT(CONCAT(f),",",";"),0)),0),
h,BYROW(g,LAMBDA(g,SUM(g))),
VSTACK(HSTACK("Group","Quantity","Amount"),HSTACK("Group "&SEQUENCE(5),FILTER(d,d>0),FILTER(h,h>0))))
inspired from Bo Rydobon,
=LET(
_input, A2:B20,
_qty, DROP(_input, , -1),
_amt, DROP(_input, , 1),
_e1, LAMBDA(arr, SCAN(0, arr, LAMBDA(a, v, IF(v, a) + v))),
_runtot, HSTACK(_e1(_qty), _e1(_amt)),
_cond, N(_qty <> 0),
_cri, VSTACK(DROP(_cond, 1) < DROP(_cond, -1), 1),
_res, FILTER(_runtot, _cri),
VSTACK(HSTACK("Group", A1:B1), HSTACK("Group" & SEQUENCE(ROWS(_res)), _res))
)
I have come up with a much shorter formula than my original formula for this challenge…
=LET(f,LAMBDA(a,BYROW(0&TEXTSPLIT(CONCAT(a&"+0"),,"+0+0",1),LAMBDA(r,SUM(0+TEXTSPLIT(@r,"+"))))),VSTACK(HSTACK("Group",A1:B1),HSTACK("Group "&SEQUENCE(COUNT(f(A2:A20))),f(A2:A20),f(B2:B20))))
Power Query
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Group = Table.Group (
Source,
"Quantity",
{ "R", each [ Quantity = List.Sum ( [Quantity] ), Amount = List.Sum ( [Amount] ) ] },
0,
( x, y ) => Number.From ( y = null )
),
Select = List.Select ( Group[R], each [Quantity] <> null ),
Output = List.Transform (
{ 1 .. List.Count ( Select ) },
each [ Group = Number.ToText ( _, "Group 0" ) ] & Select{_ - 1}
),
Return = Table.FromRecords ( Output )
in
Return
PQ_Challenge_75.xlsx (23.9 KB)
