Forum Discussion
Insert a new row if 2 fields are field
Hello, I have a complex question I tried many solutions (please see below). I want to check if 2 fields are filled (not empty), if it is the case, I want to create a new row : 1. In the...
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECImcQ7WKoFKsDFDMCsp0iQKIglosRRNQYJBoFEgWxXIwhoiYgUROIIS4mSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C" = _t, #"Col D" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if ([Col B]<>"" and [Col B]<>null) and ([Col C]<>"" and [Col C]<>null) then {1,2} else null), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), Custom1 = Table.ReplaceValue(#"Expanded Custom",each [Col C],each if [Custom] = 1 then null else [Col C],Replacer.ReplaceValue,{"Col C"}), Custom2 = Table.ReplaceValue(#"Custom1",each [Col B],each if [Custom] = 2 then null else [Col B],Replacer.ReplaceValue,{"Col B"}), #"Removed Columns" = Table.RemoveColumns(Custom2,{"Custom"}) in #"Removed Columns"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lECImcQ7WKoFKsDFDMCsp0iQKIglosRRNQYJBoFEgWxXIwhoiYgUROIIS4mSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Col B" = _t, #"Col C" = _t, #"Col D" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if ([Col B]<>"" and [Col B]<>null) and ([Col C]<>"" and [Col C]<>null) then {1,2} else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
Custom1 = Table.ReplaceValue(#"Expanded Custom",each [Col C],each if [Custom] = 1 then null else [Col C],Replacer.ReplaceValue,{"Col C"}),
Custom2 = Table.ReplaceValue(#"Custom1",each [Col B],each if [Custom] = 2 then null else [Col B],Replacer.ReplaceValue,{"Col B"}),
#"Removed Columns" = Table.RemoveColumns(Custom2,{"Custom"})
in
#"Removed Columns"Okey, you create a list of 1 and 2 for each row where the value of B and C are not null/empty. Thank you so much!