Forum Discussion
Multiple null replacements from column pairs
Hi, I'm trying to solve a fairly simple problem, but I've not been able to find the right combination of commands in the forum so far.
I have many columns in the format {"A.1", "B.1", "C.1", ...}, some of which are filled with null - I'd like to replace these values from a corresponding column, let's call them {"A.2", "B.2", "C.2", ...}. I can replace them one by one, like in the toy example here:
let
Source = Table.FromRecords({
[A.1 = 1, B.1 = null, C.1 = 3, A.2 = 1, B.2 = 2, C.2 = 3],
[A.1 = null, B.1 = 2, C.1 = 3, A.2 = 4, B.2 = 5, C.2 = 6],
[A.1 = 1, B.1 = null, C.1 = null, A.2 = 7, B.2 = 8, C.2 = 9]
}),
#"Replaced nulls in A.1" = Table.ReplaceValue(Source, each [A.1], each if [A.1] = null then [A.2] else [A.1], Replacer.ReplaceValue, {"A.1"}),
#"Replaced nulls in B.1" = Table.ReplaceValue(#"Replaced nulls in A.1", each [B.1], each if [B.1] = null then [B.2] else [A.1], Replacer.ReplaceValue, {"B.1"}),
#"Replaced nulls in C.1" = Table.ReplaceValue(#"Replaced nulls in B.1", each [C.1], each if [C.1] = null then [C.2] else [C.1], Replacer.ReplaceValue, {"C.1"})
in
#"Replaced nulls in C.1"
i.e., I'd like to replace nulls in column A.1 with the corresponding row in column A.2, etc.
But as I have many tens of columns and thousands of rows, I'd like to do the above steps in in a more efficient manner, if possible. Is there a way to fill in the nulls from the paired columns in one step? I was thinking there must be a way to iterate through a paried mapping of columns or similar (such as in a zipped list), but I couldn't get the formula set up correctly. Please can you give me some advice?
Hi BlueSkies3, try this:
Edit these 3 steps. You can probably delete ReplacedBlankToNull step.
Before
After
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcjYCEcZAIsQQRIC4IcZKsTrRSs6GIBGwNEQeogCiAq7EEGwIWBSiCKoKqgyoLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cat1 [r]" = _t, #"Cat2 [r]" = _t, #"Cat3 [r]" = _t, TargetCat_No1 = _t, TargetCat_No2 = _t, TargetCat_No3 = _t]), nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"}, valReplacementColumns = {"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"}, StepBack = Source, ReplacedBlankToNull = Table.ReplaceValue(StepBack,"",null,Replacer.ReplaceValue,{"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"}), ReplaceValuesFromTarget = List.Accumulate( List.Zip({ nullReplacementColumns, valReplacementColumns }), ReplacedBlankToNull, (s,c)=> Table.ReplaceValue(s, null, each Record.Field(_, c{1}) , (x,y,z)=> x??z, {c{0}}) ) in ReplaceValuesFromTarget
9 Replies
Hi,
let
Source = Table.FromRecords({
[A.1 = 1, B.1 = null, C.1 = 3, A.2 = 1, B.2 = 2, C.2 = 3],
[A.1 = null, B.1 = 2, C.1 = 3, A.2 = 4, B.2 = 5, C.2 = 6],
[A.1 = 1, B.1 = null, C.1 = null, A.2 = 7, B.2 = 8, C.2 = 9]
}),
Replace_null=List.Accumulate(
List.Select(Table.ColumnNames(Source), each Text.EndsWith(_,".1")),
Source,
(state,current) => Table.ReplaceValue(state, null,
each Record.Field(_, Text.Replace(current, ".1", ".2")), (x, y, z) => x??z, {current}))
in
Replace_nullStéphane
Hi BlueSkies3, i know that slorin solved your task but maybe you will also need this:
If you have more than 2 pairs of columns and you want to fill all null columns with value from pair column with higher number, this should work.
Result
let Source = Table.FromRecords({ [A.1 = 1, B.1 = null, C.1 = 3, A.2 = 1, B.2 = null, C.2 = 3, A.3 = 6, B.3 = 3, C.3 = 8], [A.1 = null, B.1 = 2, C.1 = 3, A.2 = 4, B.2 = 5, C.2 = null, A.3 = 7, B.3 = 4, C.3 = 2], [A.1 = 1, B.1 = 3, C.1 = null, A.2 = 7, B.2 = null, C.2 = 9, A.3 = 6, B.3 = 9, C.3 = 8] }), Zip = [ a = Table.ColumnNames(Source), occurence = List.Skip(List.Reverse(List.Distinct(List.Transform(a, each Text.AfterDelimiter(_, "."))))), column = List.Distinct(List.Transform(a, each Text.BeforeDelimiter(_, "."))), result = List.Combine(List.Transform(occurence, each List.Zip({ List.Repeat({_}, List.Count(column)), column }))) ][result], StepBack = Source, ReplaceNulls = List.Accumulate(Zip, StepBack, (s,c)=> Table.ReplaceValue(s, null, each Record.Field(_, c{1} & "." & Text.From(Number.From(c{0})+1)), (x,y,z)=> x??z, {c{1} & "." & c{0}} )) in ReplaceNullsThank you both for your suggestions. The column names were actually just examples. Please could you show me how I might be able to map from and to arbitrary columns, where a simple replacement of ".1" --> ".2" won't work? Thank you very much!
You should create column pairs table i.e.:
We can help you but you should create new sample with real column names - just use dummy data.
From To Column1 Column5
Column2 Column6
Column3 ColumnXX
Thank you very much! As an example, could I map from "nullReplacementColumns" to "valReplacementColumns"? I guess there is a way to zip the columns together or similar?
nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"} valReplacementColumns ={"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"}
using Table.TransformRows
let s = your_table, nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"}, valReplacementColumns = {"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"}, f = (r) => Record.TransformFields( r, List.Transform( List.Zip({nullReplacementColumns, valReplacementColumns}), (x) => {x{0}, (w) => w ?? Record.Field(r, x{1})} ) ), z = Table.FromRecords(Table.TransformRows(s, f)) in z
