Changing Formula

[SiGill1979]

I have a complex Power Query formula which determines my columns based on the week number we are in.

It basically looks at the week I am in and gets the columns for 2 weeks before the current week and 5 weeks after the current week.

So today it will bring back Columns 3, 4, 5, 6, 7, 8, 9 and 10 Next week it will bring back 4, 5, 6, 7, 8, 9, 10 and 11

I am now trying to write a formula that add a certain amount on to each column

Written normally the formula would be

(([4]/2)*10)

but that [4] will change to [5] next week and so on.

How do I write something that can change the column based on the week I am in?

[artemus]

You can do this something like:

= Table.TransformRows(PreviousStep, each _ & Record.TransformFields(_, List.Zip({List.Range(Record.FieldNames(_), <start index>, <end index or null for the end>), each _ / 2 * 10})))

That is:

For each row, modify it with (Table.TransformRows): 

• Take the current row: _
• Modify/Add  (we don't do any Add: &
• Modify the fields: Record.TransformFields
  • using the current row: _
  • Change 2 lists into a list of pairs: List.Zip
    • Limit the rows to change to a subrange: List.Range
      • Get the Column names: Record.FieldNames(_)
      • With start index: <start index>
      • And optionally end index: <end index>
    • Change the meaning of _ to point the the current cell, and run the function: each
    • Do cell modification: _ / 2 * 10

[v-lid-msft]

Hi @SiGill1979 ,

Do you want to select dynamic columns from source table or calculate a result from dynamic columns?

Could you please provide a mockup sample  based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.
 
Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[az38]

@SiGill1979 

sorry, Im  not getting what do you exactly want and how do you see desired output, but i already hae a question. why dont you just multiply to 5? like [4]*5, why ([4]/2)*10 ?

do not hesitate to give a kudo to useful posts and mark solutions as solution