Forum Discussion
Helper IVHow to define dynamically sum of last five columns
Hi All,
I need to create a table in power bi where need to show data of last five weeks on weekly basis. Just like, I did in the picture below. This I have prepared these columns in power query then loaded to desktop. Now, everyweek I need to revmove the most previous week (i.e-week 18) then add new week (i.e-week 23) and need to show sum in canceled sailing column from week 19-23 same in scheduled sailing col. For this, everyweek when next week data is updated in the source file then I have to open power query then do calculation as per the recent week (19-23) then in power bi desktop also remove col of week 18 and add new week col (week 23) to show the current data. This takes a lot of time each week.
I want to make it dynamic, means I just need to refresh then new week data is added in the table. I do not need to open power query each time.
I thought of a solution like creating a saparate data col for week and data for cancel sailing would use it.
I need to know a function which is used for adding based on last five columns. or any other way of getting this done.
Regards,
Hi Rahul_SC ,
What you can do in this case is to add the remove column step.
To turn the step into dynamically pick up the last six weeks:
Add a custom step:
Table.RemoveColumns(Previous step, List.RemoveLastN(List.Sort(List.Select(Table.ColumnNames(Previous step), each Text.Contains(_,"Week ")), Order.Ascending),6))
Let me know if this helps you to resolve your challenge.
Regards
KT
For dynamically adding last 5 columns, use below formula (you can keep adding columns and formula is not required to be changed)
= List.Sum(List.LastN(Record.ToList(_),5))
3 Replies
- KT_Bsmart2gethe
Impactful Individual
Hi Rahul_SC ,
What you can do in this case is to add the remove column step.
To turn the step into dynamically pick up the last six weeks:
Add a custom step:
Table.RemoveColumns(Previous step, List.RemoveLastN(List.Sort(List.Select(Table.ColumnNames(Previous step), each Text.Contains(_,"Week ")), Order.Ascending),6))
Let me know if this helps you to resolve your challenge.
Regards
KT
Rahul_SCVijay_A_Verma , thanks for responding.
KT_Bsmart2gethe a big thanks to Kirwin. You helped me a lot from the beginning till my end outcome what I excatly wanted.
- Vijay_A_Verma
Most Valuable Professional
For dynamically adding last 5 columns, use below formula (you can keep adding columns and formula is not required to be changed)
= List.Sum(List.LastN(Record.ToList(_),5))