Zero out subset of columns

[Nick Burns]

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.

 

[Nick Burns]

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

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

 

[AliGW]

Do you havee a sample workbook to share?

 

[Nick Burns]

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})

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)

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

 

[AliGW]

Can you please attach a sample workbook?

 

[Nick Burns]

Can you please attach a sample workbook?

Hi ALiGW - The above PQ code creates a sample table - no need for a sample workbook.

 

[AliGW]

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.

 

[Nick Burns]

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:

 

[AliGW]

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.