Dynamic conditional replace function

[seaottr]

Hi Power Query gurus out there!

I'm going to do my best to explain some help that I need it replacing values, conditionally based on another column's values, while dynamically referencing field names.

Here's some sample data:

Date	Greeting	Appreciate Patience (if hold was used)	Resolution Statement	Discuss Follow-Up	Greeting Max Points	Appreciate Patience (if hold was used) Max Points	Resolution Statement Max Points	Discuss Follow-Up Max Points
8-22-2022​	5​	0​	20​		5​	5​	20​	20​
8-24-2022​	5​	2.5​	20​		5​	5​	20​	20​
8-23-2022​	5​	5​	0​	20​	5​	5​	20​	20​
8-24-2022​	5​		20​	20​	5​	5​	20​	20​
8-25-2022​	5​	2.5​	0​		5​	5​	20​	20​
8-26-2022​	5​	5​			5​	5​	20​	20​

I want to replace any values in the "Max Points" columns (the last 4) with zero if the corresponding non-"Max Points" columns' values are null. I need this to be dynamic in case I add more fields. The "Max Points" fields will always be identical to the non-"Max Points" fields with the exception of the " Max Points". I believe I may need to use a function like "each Record.Field", but not sure how to make it dynamic enough to replace the values with zero in the "Max Points" field by looking for nulls in the same field name without " Max Points".

I totally understand how complicated I made that sound, but not sure how else to phrase it...LOL!

So in the above above data set, I want to replace the 4th record in "Appreciate Patience (if hold was used) Max Points" with zero because "Appreciate Patience (if hold was used)" is null. Similarly, records 1, 2, 5 and 6 in "Discuss Follow-Up Max Points" should be zero and same with the last record for "Resolution Statement Max Points"

I understand I can do this by referencing each column separately, but I want this to be dynamic enough for future changes.

Any help would be greatly appreciated! Thank you in advance!

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MaxPointsColNames = List.Select(Table.ColumnNames(Source), each Text.EndsWith(_,"Max Points")),
    ColNames = List.Transform(MaxPointsColNames, each Text.Start(_, Text.Length(_) - 11)),
    lst = Table.TransformRows(Source, each 
        List.Accumulate(ColNames, _, (s,c)=> if Record.Field(s, c) = null then Record.TransformFields(s, {{c & " Max Points", (x)=> 0}}) else s)),
    Result = Table.FromRecords(lst)
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J
1	Date	Greeting	Appreciate Patience (if hold was used)	Resolution Statement	Discuss Follow-Up	Greeting Max Points	Appreciate Patience (if hold was used) Max Points	Resolution Statement Max Points	Discuss Follow-Up Max Points
2	8-22-2022	5	0	20		5	5	20	20
3	8-24-2022	5	2.5	20		5	5	20	20
4	8-23-2022	5	5	0	20	5	5	20	20
5	8-24-2022	5		20	20	5	5	20	20
6	8-25-2022	5	2.5	0		5	5	20	20
7	8-26-2022	5	5			5	5	20	20
8
9	Date	Greeting	Appreciate Patience (if hold was used)	Resolution Statement	Discuss Follow-Up	Greeting Max Points	Appreciate Patience (if hold was used) Max Points	Resolution Statement Max Points	Discuss Follow-Up Max Points
10	8-22-2022	5	0	20		5	5	20	0
11	8-24-2022	5	2.5	20		5	5	20	0
12	8-23-2022	5	5	0	20	5	5	20	20
13	8-24-2022	5		20	20	5	0	20	20
14	8-25-2022	5	2.5	0		5	5	20	0
15	8-26-2022	5	5			5	5	0	0
16
Sheet1

 

[seaottr]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MaxPointsColNames = List.Select(Table.ColumnNames(Source), each Text.EndsWith(_,"Max Points")),
    ColNames = List.Transform(MaxPointsColNames, each Text.Start(_, Text.Length(_) - 11)),
    lst = Table.TransformRows(Source, each
        List.Accumulate(ColNames, _, (s,c)=> if Record.Field(s, c) = null then Record.TransformFields(s, {{c & " Max Points", (x)=> 0}}) else s)),
    Result = Table.FromRecords(lst)
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J
1	Date	Greeting	Appreciate Patience (if hold was used)	Resolution Statement	Discuss Follow-Up	Greeting Max Points	Appreciate Patience (if hold was used) Max Points	Resolution Statement Max Points	Discuss Follow-Up Max Points
2	8-22-2022	5	0	20		5	5	20	20
3	8-24-2022	5	2.5	20		5	5	20	20
4	8-23-2022	5	5	0	20	5	5	20	20
5	8-24-2022	5		20	20	5	5	20	20
6	8-25-2022	5	2.5	0		5	5	20	20
7	8-26-2022	5	5			5	5	20	20
8
9	Date	Greeting	Appreciate Patience (if hold was used)	Resolution Statement	Discuss Follow-Up	Greeting Max Points	Appreciate Patience (if hold was used) Max Points	Resolution Statement Max Points	Discuss Follow-Up Max Points
10	8-22-2022	5	0	20		5	5	20	0
11	8-24-2022	5	2.5	20		5	5	20	0
12	8-23-2022	5	5	0	20	5	5	20	20
13	8-24-2022	5		20	20	5	0	20	20
14	8-25-2022	5	2.5	0		5	5	20	0
15	8-26-2022	5	5			5	5	0	0
16
Sheet1

Sorry for the late reply JGordon11! I was done work for the weekend when you responded, so I didn't get a chance to try out your solution until now.

You, Sir, are a GENIUS!!!! Worked liked a charm and I can't begin to thank you enough for coming up with the solution! There's no way I would have EVER been able to figure that out. I'm amazed by your skills and expertise! Thank you SO much for your help with this!!!