Forum Discussion
Solved
Replace Null Values and If
Hello, I am a novice user of PQ. What I want to do is: change where [Budget Category] = Null and if [Vendor] = "Co A" then change [Budget Category] to "A" and if [Vendor] = "Co B" then change [...
Better use a reference table, and a custom join/lookup . No need to hard code this.
Hi Antrik,
thank you for the response. Interesting approach. I am a novice user and I'm not a programmer and I don't really understand what the code is doing, but I do not see how it is making the required changes. Thank you
Centaur Based on your pattern it looks like you only need the last alphabet/word in that string so you don't need to use conditional logic here instead you can extract the last charcater with Text.End if it is an alphabet and if it is a word then you can split it with Text.Split and take the last entry in the list.
let
Source = Table.FromRows (
Json.Document (
Binary.Decompress (
Binary.FromText (
"i45Wcs5XcFTSUYoAAqVYHTDfCciHsZ2B7EgQgAm4gCVjAQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [ Vendor = _t, #"Budget Category" = _t ]
),
ReplacedValue = Table.ReplaceValue (
Source,
"",
null,
Replacer.ReplaceValue,
{ "Budget Category" }
),
If_Alphabet = Table.AddColumn (
ReplacedValue,
"Alphabet",
each
if [Budget Category] is null
then Text.End ( [Vendor], 1 )
else [Budget Category],
type text
),
If_Word = Table.AddColumn (
If_Alphabet,
"Word",
each
if [Budget Category] is null
then List.Last ( Text.Split ( [Vendor], " " ) )
else [Budget Category],
type text
)
in
If_Word
Just to add a little bit more information, I am not trying to change the budget category if null to the vendor field.