Forum Discussion
Help with Implementing Reiteration for Conditional Replacement of Values through "Coupled" Columns
let replacer = (text01, text02) => Text.Replace(text01 ?? "", text02 ?? "", ""), fxr = (lst) => ( (data) => {lst{0}} & List.Transform(List.Zip(data), (x) => replacer(x{0}, x{1})) & data{1} )(List.Split(List.Skip(lst), (List.Count(lst) - 1) / 2)), Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content], ttl = Table.ToList(Source, fxr), result = #table(Value.Type(Source), ttl) in result
Super Userlet
replacer = (text01, text02) => Text.Replace(text01 ?? "", text02 ?? "", ""),
fxr = (lst) => (
(data) => {lst{0}} & List.Transform(List.Zip(data), (x) => replacer(x{0}, x{1})) & data{1}
)(List.Split(List.Skip(lst), (List.Count(lst) - 1) / 2)),
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
ttl = Table.ToList(Source, fxr),
result = #table(Value.Type(Source), ttl)
in
resultHi AlienSx ,
First of all - thank you so much for your input. Second of all - I apologize in advance because I'm not as M savvy as most people here are so I'd probably will need some hand holding to understand the code.
While I attempted to implement your code, I realized that I did not mention in my original post that:
- I have other columns in the table aside from the A & B Columns in my actual table. I removed the other columns in the example table in my post so that it's much easier to explain.
- The "blanks" in my columns are actually "null"s
I implemented your code and modified the "Source" line to the table, and it seems to work! However, several things that I was wondering:
- I had to remove the other columns so that the table only consists of the primary key (Data1, Data2, ...) and the coupled columns. I would have to re-merge the end result to the beginning of the table, would there be an easier way to do this?
- All the ones that are "null" in the table ended up being a blank field and not a "null". Is there a way that it would stay "null" instead of blank?
Both of these I can definitely just implement as separate steps in the code, but wondering if we could modify the functions instead.
Thanks again for your help and sorry for the late response!
- AlienSx
hello, isg
1. Other columns: you don't need to remove other columns. E.g. use Table.CombineColumnsToRecord to combine all other columns (including primary key) into single column, make sure that this new column goes first, apply table transformation and then expand that column using Table.ExpandRecordColumn.
2. Modify replacer function so that it checks if text01 is null or text01 = text02 (result must be null), text02 is null (result must be text01) otherwise result must be Text.Replace(text01, text02, "")
