در این پست به این موضوع می پردازیم که چطور داده ها رو در مرحله پردازش و قبل از ورود به محیط مدلسازی در power query جمع و ضرب کنیم (البته مثال سادست ولی پروسه برای مسائل دیگه کمک کنندست). جمع کردن چند تا ستون که زیاد سخت نیست ولی اگر بخواهیم به طور مثلا دو لیست از داده رو دو به دو ضرب کنیم و نتایج رو جمع کنیم چه کنیم؟
نسخه مورد استفاده در این مثال : Power BI June 2020
اول از همه بریم سراغ تابع اصلی یعنی List.Accumulate که سینتکس آن به صورت زیر می باشد :
|
1 |
List.Accumulate(list as list, seed as any, accumulator as function) as any |
به طور خلاصه میشه گفت کاربردش اینه که منطق یا تابعی که بر روی لیست اعمال میشه رو به صورت خلاصه به ما بر می گردونه یعنی چی؟ مثلا کد زیر مجموع اعداد 1 تا 5 رو برمیگردونه (خروجی 15 میشه جهت اطلاع!).
|
1 |
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) |
در مثال بالا لیستی داریم شامل اعداد 1 تا 5 با مقدار seed برابر 0 که در واقع همان مقدار اولیه شمارنده یا به صطلاح accumulator هستش. در قسمت سوم تابع نیز در واقع تابع مورد نظر که در اینجا جمعه نوشته شده. نگاه دقیق تر به عملکرد تابع نشان می دهد که در این مثال تابع در مراحل مختلف به این صورت کار می کند (به صورت تجمعی عمل میکنه) :
|
1 2 3 4 5 |
iteration 1 : state = seed = 0 + current = 1 => state =1 iteration 2 : state = 1 + current = 2 => state =3 iteration 3 : state = 3 + current = 3 => state =6 iteration 4 : state = 6 + current = 4 => state =10 iteration 5 : state = 10 + current = 5 => state =15 |
کد رو به این صورت می تونید چک کنید :
|
1 2 3 4 5 |
let calculation=List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current), #"Converted to List" = {calculation} in #"Converted to List" |
حالا اگر seed رو مثلا 2 قرار بدید یعنی state اول شما 2 خواهد بود و نتیجه 17 خواهد شد.
مثال دیگر :
|
1 2 3 4 5 |
let calculation=List.Accumulate({10,20,30},5,(state, current)=>(if state < 25 then state+current else state*current)), #"Converted to List" = {calculation} in #"Converted to List" |
که به این صورت عمل میکنه. به seed هم دقت کنید.
|
1 2 3 |
iteration 1 (state=seed=5,current=10) : if 5 < 25 => state=5+10=15 iteration 2 (state=15,current=20) : if 15 < 25 => state=15+20=35 iteration 3 (state=35,current=30) : if 35 < 25 => state=35*30=1050 |
حالا بریم سراغ مثال و بحث پست و اون هم sumproduct می باشد :
|
1 2 3 4 5 6 7 8 9 10 11 |
let List1 = {1,2,3,4,5}, List2 = {5,4,3,2,1}, result = List.Accumulate( List1, [Sum=0, Index=0], (state, current) => [Sum = current * List2{state[Index]}+state[Sum], Index = state[Index]+1] )[Sum] in result |
یه فرقی بوجود اومد و اون هم اینه seed به صورت رکورد وارد شده و در اینجا دو مقدار داریم مقدار اول Sum که نتیجه خروجی رو به ما میده با مقدار اولیه 0 و مقدار دوم Index به عنوان counter یا شمارنده عمل می کنه. به روند محاسبه دقت کنید :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
iteration 1 : state(Sum=0, Index=0), current=1 Sum = current * List2{state[Index]}+state[Sum] = 1 * List2{0}+0 = 1*5+0=5 Index = state[Index]+1 = 0 + 1 = 1 iteration 2 : state(Sum=5, Index=1), current=2 Sum = current * List2{state[Index]}+state[Sum] = 2 * List2{1}+5 = 2*4+5=13 Index = state[Index]+1 = 1 + 1 = 2 iteration 3 : state(Sum=9, Index=2), current=3 Sum = current * List2{state[Index]}+state[Sum] = 3 * List2{2}+9 = 3*3+13=22 Index = state[Index]+1 = 2 + 1 = 3 iteration 4 : state(Sum=22, Index=3), current=4 Sum = current * List2{state[Index]}+state[Sum] = 2 * List2{3}+19 = 4*2+22=30 Index = state[Index]+1 = 3 + 1 = 4 iteration 5 : state(Sum=23, Index=4), current=5 Sum = current * List2{state[Index]}+state[Sum] = 2 * List2{3}+19 = 5*1+30=35 Index = state[Index]+1 = 4 + 1 = 5 |
چند نکته :
-
- مقدار Sum آخر که در [] قرار داده شده یعنی مقدار Sum رو برگردون که اگر با Index جایگزین شود مقدار آخرین شمارنده یعنی 5 را باز می گرداند.
- در عبارت List2{state[Index]} در واقع مقدار ایندکس لیست دوم رو بر میگردونه و جهت اطلاع ایندکس ها در اینجا از صفر شروع می شوند.
- همچنین مقدار state[Sum] در اصل مقدار state با نام Sum رو بر می گردونه.
