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 UserHere's another method that seems to work a bit faster.
It uses List.Accumulate to iterate the Table.ReplaceFunction to check each pair of columns.
Note that I use List.RemoveMatchingItems function. As written, it will only remove if there is a "perfect" match, as you wrote. If you want to make the match case-insensitive, or include other imperfections, you can include the optional Equation Criteria.
However, if your actual layout is different, and/or if you have other columns in your actual data that are not relevant to your problem. That algorithm will need to be changed.
Please read the code comments, explore the applied steps, and ask if you don't understand something.
let
//Change next line to reflect your actual data source
Source =Table,
/*Written to accomodate any number of columns, but assumes they are all type text
If you have other columns in your real data that are of a different type, this
will need modification*/
#"Changed Type" = Table.TransformColumnTypes(Source,
List.Transform(Table.ColumnNames(Source), each {_, type nullable text})),
/*If your real data has more columns that are not to be checked
this algorithm to get the column pairs will need to be changed*/
#"Columns" = [a=List.Skip(Table.ColumnNames(Source)),
b=List.Split(a,List.Count(a)/2),
c=List.Zip(b)][c],
#"Replace Matches" = List.Accumulate(
#"Columns",
#"Changed Type",
(s,c)=> Table.ReplaceValue(
s,
each Record.Field(_,c{0}),
each Record.Field(_,c{1}),
(x,y,z) as nullable text=> [a=Text.Split(y,"#(lf)"),
b=Text.Split(z,"#(lf)"),
c=List.RemoveMatchingItems(a,b),
d=Text.Combine(c,"#(lf)")][d],
{c{0}}
)
)
in
#"Replace Matches"Original
Results
v-aatheequeCommunity Support
Hi isg
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.- v-aatheeque
Hi isg
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.Hi! Sorry for the late response. I will still need to look into this solution. Was away for Thanksgiving this past week and didn't get the chance to test this. Will report back when I've successfully implemented it.
Hi ronrsnfld ,
First of all - Thank you so much for your input! Apologies for the late response, it took me a little bit to understand how to implement your code in my file, as I am not that code savvy.
The example data/table that I've put in the original post is a simplified version of the actual data. I have about 155 columns in my data that are not part of the "coupled columns" and 23 pair of the "coupled columns". All of the 23 pair "coupled columns" have text as their data type. The rest of the 155 columns are either Int64.Type, text, date, or number. The primary key of the table is an Int64.Type.
Additionally, the "coupled columns" are actually in two separate tables, but we can easily merge them as they use the same primary key. I merged them through left join, so there will be cases like Data3, where Table A (with columns Ax, Ay, and Az) do not have an accompanying pair in Table B (columns Bx, By, and Bz).
I've modified your code to take the above facts into account:
let
Source = Table.NestedJoin(TableA, {"PrimaryKey"}, TableB, {"PrimaryKey"}, "TableB", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Source, "BColumns", TableBHeaders),
FinalSource = Table.SelectColumns(Expand, //List of Relevant Columns),
Columns = List.Zip({TableAHeaders, TableBHeaders}),
#"Replace Matches" = List.Accumulate(
Columns,
FinalSource,
(s,c)=> Table.ReplaceValue(
s,
each Record.Field(_,c{0}),
each Record.Field(_,c{1}),
(x,y,z) as nullable text=> [a=Text.Split(y,"#(lf)"),
b=Text.Split(z,"#(lf)"),
c=List.RemoveMatchingItems(a,b),
d=Text.Combine(c,"#(lf)")][d],
{c{0}}
)
)
in
#"Replace Matches"Your approach is what I had in mind when I first encountered this problem. I had figured out how to list.zip the headers but was not competent enough to understand how to use List.Accumulate and to build my own replacement function. I tried removing your change type step and the code seems to be working just fine without that step, but let me know of any edge cases I need to be aware of - so that I understand why we need to keep that step.
The only part that I can't figure out when modifying your code is which line I need to modify when I want the blanks to come up as "null" instead of blanks. As I wrote in the other comment, I can certainly do another step where I null all the blanks, but wondering if I can just write it in the same step instead.
Appreciate any help moving forward!
- ronrsnfld
In general, Power Query works better if it knows the data types it is working on. But I don't believe that removing the #"Changed Type" step in my code will cause any problems. If you have many, many rows in your data, you could check to see if explicitly setting just the coupled columns to type text before the #"Replace Matches" steps makes a difference in execution time. I suspect it will not.
And yes, with a more complex replacement line in the #"Replace Matches" step you could wind up with nulls instead of blanks in the replaced items. But I don't have time this morning to look at it further. And I don't know which method would be more efficient (replace in that step or replace all the blanks with nulls as a final step). I'll take a look at it later.
- v-aatheeque
Hi isg
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further...
- ronrsnfld
I ran some tests performing the replacement of blanks with nulls within the Table.ReplaceValue function.
You can do it within that function by replacing the "d=" line as below:
d=if List.IsEmpty(c) then null else Text.Combine(c,"#(lf)")However, this method takes more time than merely replacing the blanks with nulls when you have finished processing the table.
A difference, of course, is that replacing it within the Table.ReplaceValue function will replace only those values that were removed because they match; whereas doing it afterwards will replace ALL of the blanks with nulls, whether they were in the original data or not.
