write a macro or function in Power Query m

[elaj]

Hi,

i often have repetitive tasks to do.

example:

F2_start_date_BRAND1 = Table.AddColumn(match_IDs_raus, "F2_start_date_BRAND1",
each if [w1.F2_NUM_BRAND1] <> null then [w1.Date]
else if [w2.F2_NUM_BRAND1] <> null then [w2.Date]
else if [w3.F2_NUM_BRAND1] <> null then [w3.Date]
else if [w4.F2_NUM_BRAND1] <> null then [w4.Date]
else if [w5.F2_NUM_BRAND1] <> null then [w5.Date]
else if [w6.F2_NUM_BRAND1] <> null then [w6.Date]
else null),

F2_start_value_BRAND1 = Table.AddColumn(F2_start_date_BRAND1, "F2_start_value_BRAND1",
each if [w1.F2_NUM_BRAND1] <> null then [w1.F2_NUM_BRAND1]
else if [w2.F2_NUM_BRAND1] <> null then [w2.F2_NUM_BRAND1]
else if [w3.F2_NUM_BRAND1] <> null then [w3.F2_NUM_BRAND1]
else if [w4.F2_NUM_BRAND1] <> null then [w4.F2_NUM_BRAND1]
else if [w5.F2_NUM_BRAND1] <> null then [w5.F2_NUM_BRAND1]
else if [w6.F2_NUM_BRAND1] <> null then [w6.F2_NUM_BRAND1]
else null),

etc...

now i want to replace BRAND1 with BRAND2 and so on.. in spss i have the option to write macros, to use syntax text multiple times.

Is there a way for M too to write a macro/function/loop like that?

Thanks for your answers

[mahoneypat]

Are your different Brands in the same table?  This looks like you are doing calculations across a number of columns.  I would unpivot the data, which should remove your nulls (if not, just filter them out), and then group by on brand, doing some aggregation on the unpivoted Date column.

If you had to do that across multiple tables, you could make it a reusable function.  But if they are all in the same table, you can do it all together.  If you provide example data and desired output, a more specific solution can be provided (by me or someone in the community).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[elaj]

i tried something like this:

let
Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLCMwyBrKMwSwTIMsEzDIFskyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Bla_BRAND1 = _t, Bla_BRAND2 = _t]),
Geänderter_Typ = Table.TransformColumnTypes(Quelle,{{"Bla_BRAND1", Int64.Type}, {"Bla_BRAND2", Int64.Type}}),

test = List.Accumulate({"BRAND1", "BRAND2"}, Geänderter_Typ, (state, current) => Table.AddColumn(state, current, each if [Bla_current] = 1 then 93 else null))

in
test

but current cannot be used inside of an column identifier

[artemus]

You can use:

Record.Field(_, "My Column Name")

Which is the same as [My Column Name]