Zero out subset of columns

Nick Burns

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
I have 21 columns named for hours worked per day (Regular, Overtime, Double), named as such
rday__1 .. rday__7
oday__1 .. oday__7
dday__1 .. dday__7

This main table will be split into two tables.

Based on a passed parameter, I need to zero out a subset of these columns. For instance if the parameter is 5 I need:
table 1: zero out columns xday__5 .. xday__7
table 2: zero out columns xday__1 .. xday__4

I guess I'm looking at how to pass a subset list of columns to the TransformColumns() to zero them out so that it generates a list of
{"xday__5", each 0, type number} .. {"xday__7", each 0, type number}} for table 1
{"xday__1", each 0, type number} .. {"xday__4", each 0, type number}} for table 2

I can visualize the solution, I just can't seem to translate it correctly to m code.

Thanks for any insight.
 
So I'm 90% the way there... I just can't figure out how to return the each 0 to the list as a function

Code: 
let
    SplitDay = 5,
    Source = List.Combine( {List.Transform({1..SplitDay}, each "rday__" & Number.ToText(_)),List.Transform({1..SplitDay}, each "oday__" & Number.ToText(_)),List.Transform({1..SplitDay}, each "dday__" & Number.ToText(_))}),
    Custom1 = List.Zip({Source,List.Transform({1..List.Count(Source)}, each type number )})
in
    Custom1
 
Last edited:
I think I've come up with a solution, but I'll welcome any improvements, perhaps to the List.Combine step. Although short, it does repeat the Transform 3 times for each of the hour types.

Here's the Split1 Query (Split2, just reverse the numeric list to ({1..SplitDay})
Code: 
let
    SplitDay = 5,
    Source = List.Combine( {List.Transform({SplitDay + 1..7}, each "rday__" & Number.ToText(_)),List.Transform({SplitDay + 1..7}, each "oday__" & Number.ToText(_)),List.Transform({SplitDay + 1..7}, each "dday__" & Number.ToText(_))}),
    Custom2 = List.Zip({Source,List.Transform({1..List.Count(Source)}, each each 0),List.Transform({1..List.Count(Source)}, each type number )})
in
    Custom2

And here's the Week1 using the split (Week2 just uses Split2)

Code: 
let
    Source = #table(
 type table
    [
        #"rday__1"=number, 
        #"rday__2"=number, 
        #"rday__3"=number, 
        #"rday__4"=number, 
        #"rday__5"=number, 
        #"rday__6"=number, 
        #"rday__7"=number, 
        #"oday__1"=number, 
        #"oday__2"=number, 
        #"oday__3"=number, 
        #"oday__4"=number, 
        #"oday__5"=number, 
        #"oday__6"=number, 
        #"oday__7"=number, 
        #"dday__1"=number, 
        #"dday__2"=number, 
        #"dday__3"=number, 
        #"dday__4"=number, 
        #"dday__5"=number, 
        #"dday__6"=number, 
        #"dday__7"=number 
    ], 
 {
   {1,2,3,4,5,6,7,1,2,3,4,5,6,7,1,2,3,4,5,6,7}  
 }
),
    ZeroHours = Table.TransformColumns(Source,Split1)
in
    ZeroHours
 
Yes, so provide a workbook with your M code in it so we can have a look ... ;)

I need to see how you are using the code, because it does nothing for me except produce a list of five lists. Of course, if you just don't want me to bother, then that's absolutely fine. :)
 
Yes - I do welcome you and anyone else to look at it... in my post above, there are two queries...the first that produces the desired lists (name it 'Split1') and the second that produces a table that uses the list. Everything anyone would need is provided. :thumb:
 
Fair enough. In the time it took you to type that, you could have attached a sample file. I’m not here to play games. Good luck with it. :)
 
You must log in or register to reply here.
Back
Top