馃攳
Aging of Receivables Report using Power Query - YouTube
Channel: Lineth Dela Cruz
[0]
[Music]
[5]
hi and welcome back in this video i'm
[7]
going to show you how to make an agent
[9]
of receivables report using the power
[11]
query and also the past due dates label
[14]
function that i've created in my last
[16]
video so this is our data and as you can
[19]
see
[20]
i've already converted it into a table
[22]
so when you hit the table design tab
[25]
you can see the table name receivables
[28]
so i will select any of it and then go
[30]
to data tab
[32]
and then from table range
[35]
and then here uh the first thing is to
[38]
change the invoice date into a date type
[41]
so click the icon here and then choose
[43]
date replace current okay and then while
[46]
choosing the invoice date i will add
[48]
column and then choose the date and
[50]
choose age
[52]
okay and then convert it into whole
[54]
number
[55]
so choose the icon again before the each
[57]
and then whole number
[59]
and then okay now before we have another
[62]
column here for the past two days i will
[64]
use the function here i will set
[67]
five rows and then
[70]
an increment of 30 days and then invoke
[74]
that's it the other days and pass you
[76]
ladles now i will
[78]
rename it as revtable
[82]
okay let's go back to the receivables
[86]
and let's add our column custom column
[91]
and then let's use the custom name so i
[94]
will write it
[96]
so if my age column
[99]
is less than or equal to the increment
[102]
so the increment will be on the reft
[104]
ball
[106]
and then days column and row one which
[110]
is the second row
[112]
that will be equal always equal to the
[114]
increment that you set in the function
[117]
so if that is the case then
[121]
we will have a not you label on that
[126]
else
[127]
i will have age column minus the
[130]
increments you just copy this ref table
[133]
this one
[135]
and then okay
[138]
so that's it now um
[141]
let's uh add the past you labels so this
[144]
ref table here we have the past zoo
[147]
let's put it here on the receivables
[149]
using the approximate match
[152]
so uh go back to the receivables and i
[155]
will perform the approximate match using
[158]
the leg function so i've already done
[160]
this in my previous video get the
[163]
applicable rates using approximate match
[166]
if you want a full tutorial on that you
[168]
can watch that video because in here i
[170]
will make it very quick just to have or
[173]
to perform the approximate match so i
[175]
will add column custom column
[180]
and then
[182]
let's call it the past due
[185]
and then i will use let function
[188]
and the first variable a will be equal
[190]
to the custom column here
[194]
minus one
[197]
and then second variable will be a list
[200]
that select formula
[203]
this let's select the table will be ref
[205]
table column days
[208]
and then the condition will be each of
[210]
the items will be okay underscore
[214]
will be less than or equal to variable a
[218]
okay and then wrap it with the list at
[222]
last so that you will only have the last
[225]
value
[226]
okay and then comma last variable c will
[230]
be equal to table that select rows
[235]
then the table will be reftable and then
[238]
the condition will be each the days
[241]
in the rough table this column in the
[243]
ref table
[245]
should be equal to the variable b
[249]
okay
[250]
but we will have to just display the
[253]
past you columns
[255]
in square bracket pass you
[257]
and then
[260]
wrap it with listed single so that you
[263]
will get the single value
[266]
and then uh display it in c okay
[270]
so that's it that's the
[272]
label but as you can see we have errors
[274]
because of the not zoo label in the
[276]
custom so let's uh
[279]
use try and otherwise go back to the
[281]
added custom one
[284]
and then before the left let's say
[286]
please try the let
[288]
then otherwise at the end otherwise
[291]
choose
[292]
the custom column here so that it will
[295]
get the not you
[297]
label and then okay so that's it it's
[300]
already corrected now uh we're finished
[303]
here we can go back to the spreadsheet
[305]
so let's load it go to the home tab
[308]
close and load two
[310]
and then let's make a connection first
[312]
only create connection okay and then
[314]
choose receiver balls and then load to
[317]
and choose pivot table report choose
[320]
existing worksheet okay
[323]
now on the rows let's choose custom name
[326]
and then for the columns
[329]
the past due labels and then
[332]
the amount for the values
[334]
okay let's close the pivot table fields
[336]
and the queries and connection okay
[338]
let's quickly format it let's put the
[341]
not zoo on the
[343]
second
[350]
column push the design
[353]
like this okay
[356]
now let's check it let's check the
[359]
function now um i'm going to add
[362]
another customer and then the ebay state
[364]
will be april 1
[367]
2022
[368]
and an amount of 1000
[371]
then for manual checking
[373]
let's uh have today formula
[377]
get the day-to-day and then that's minus
[380]
the today
[381]
on the invoice date
[383]
okay and then
[385]
increment 30 minus 15 okay that's 15
[388]
days
[389]
it's gonna be on the not zoo column
[392]
so let's go to data tab and refresh all
[396]
and that's it it's added in that due
[398]
column let's change it
[400]
into january 1.
[403]
that's 75 days over so that will be in
[406]
here 61 to 90 days
[408]
refresh all
[410]
okay that is changing so uh that's it
[413]
i'm finisher i'm done if you like this
[415]
video you can subscribe in my channel
[416]
click the subscribe button and see you
[418]
next time and thanks
[422]
you
Most Recent Videos:
You can go back to the homepage right here: Homepage