Replace Null Values and If

[Centaur]

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 [Budget Category] to "B"

and if [Vendor] = "Co C" then change [Budget Category] to "C"

The above is a simplified version of what I want to do. There are about 15 different companies I will need to change the [Budget Category] where it is NULL. 

this is the last line of my code (if necessary):

How best to approach this?

Grateful for the help. 

thank you!

[lbendlin · Accepted solution]

Better use a reference table, and a custom join/lookup . No need to hard code this.

[v-hashadapu]

Hi @Centaur , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Centaur , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Centaur , Thank you for reaching out to the Microsoft Community Forum.

As mentioned by @lbendlin , the most efficient way to do this is to use a reference table that stores the mapping between Vendor and Budget Category. This avoids hardcoding multiple conditions, making updates easier if new vendors are added in the future. If you prefer a simpler approach without a reference table, you can use conditional logic in Power Query to manually define rules for each Vendor. However, this method is less efficient when dealing with too many vendors, as you need to manually update the logic whenever vendors change.

I suggest using the reference table method as the latter method works but is not scalable and requires manual updates when new vendors are added.

As always, we appreciate @lbendlin for your continuous contribution to the community.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[Centaur]

Thank you V.  Appreciate the follow up confirming most efficient approach.  I dont know exactly how to implement but I think i can figure it out.  I might need to check a youtube video.  This is my go to "advisor".  I plan to tsckle today or the weekend.  Thank you again for the response.  

[v-hashadapu]

Hi @Centaur , Thanks for the update. Always happy to help.

[Centaur]

Hi Ibendlin, that is a great idea. I think I can do it, but I might have to circle back. Thank you for your response.

[AntrikshSharma]

@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

[Centaur]

Just to add a little bit more information, I am not trying to change the budget category if null to the vendor field.

[Centaur · Accepted solution]

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 

[lbendlin · Accepted solution]

Better use a reference table, and a custom join/lookup . No need to hard code this.