Vendor 1Vendor 2 Vendor 3Vendor MasterABC BCD CDE Hi everyone, I'm trying to put up a IF formula for the following scenario. But I'm facing difficulty in getting the proper solution. It would be great if someone would help me to build a proper formula for this one. Problem statement: I have 3 columns for Vendors i.e Vendor 1, Vendor 2, Vendor 3. I want to put up a formula in "Vendor Master" such that IF "Vendor 1" is blank then it should return value from "Vendor 2" in "Master Vendor".IF "Vendor 2" is also blank then it should return value from "Vendor 3".IF "Vendor 3" is blank then it should return a string "No Vendor". It would also be great if someone could tell me how this can be done in Power BI as well. Quick response is highly appreciated.Thanks in advance.

SatishBadiger I'm pretty sure someone will have a more eloquent formula but this can be done with nested IF formula - see attached example =IF($A2>"",$A2,IF($B2>"",$B2,IF($C2>"",$C2,0)))

SatishBadiger If under Power BI you mean transformation in Power Query, you may add custom column as = Table.AddColumn( Source, "Vendor Master", each List.RemoveNulls(Record.FieldValues( _ )){0} )

Hey Charla74, thank you so much this worked!!

Hi SatishBadiger For PowerBI/Power Query, similar to SergeiBaklan with the "No vendor" exception:= Table.AddColumn(PreviousStep, "Vendor Master", each try List.RemoveNulls(Record.ToList(_)){0} otherwise "No vendor" )

SatishBadiger Record.FieldValues and Record.ToList take a Record ("row" if you prefer) and return a List containing all values from that Record, whatever the number of columns is

IF statement based on multiple columns.

Charla74
Iron Contributor
Jun 16, 2022
SatishBadiger

I'm pretty sure someone will have a more eloquent formula but this can be done with nested IF formula - see attached example

=IF($A2>"",$A2,IF($B2>"",$B2,IF($C2>"",$C2,0)))
Book1.xlsx9 KB
- SatishBadiger
  Copper Contributor
  Jun 17, 2022
  Hey Charla74, thank you so much this worked!!
  - RaquelJ
    Copper Contributor
    Nov 04, 2023
    Hi Charla74 ,
    
    How about if statement to add multiple columns? how to summarize it.
    
    Thank you so much.
SergeiBaklan
MVP
Jun 16, 2022
SatishBadiger

If under Power BI you mean transformation in Power Query, you may add custom column as

= Table.AddColumn( Source, "Vendor Master", each List.RemoveNulls(Record.FieldValues( _ )){0} )
Book1 (16).xlsx19 KB
- SatishBadiger
  Copper Contributor
  Jun 22, 2022
  SergeiBaklan
  I have 15 other columns in my dataset. Will this code still work?
  - Lorenzo
    Silver Contributor
    Jun 22, 2022
    SatishBadiger
    Record.FieldValues and Record.ToList take a Record ("row" if you prefer) and return a List containing all values from that Record, whatever the number of columns is
Lorenzo
Silver Contributor
Jun 17, 2022
Hi SatishBadiger

For PowerBI/Power Query, similar to SergeiBaklan with the "No vendor" exception:
= Table.AddColumn(PreviousStep, "Vendor Master", each try List.RemoveNulls(Record.ToList(_)){0} otherwise "No vendor" )
Leal_D
Copper Contributor
Jun 22, 2022
SatishBadiger
If you have Filter and each row has only one entry, you could use
=FILTER(A2:C2,A2:C2<>"")
Yea_So
Bronze Contributor
Apr 13, 2024
Your given is lacking context, i.e. why are the vendors in different rows what is the reason or basis for that, maybe that reason/basis would also become an integral part of the formula solution?
Yea_So
Bronze Contributor
Apr 15, 2024
SatishBadiger

So the geographical context is "Vendor Master"

Logic context is if the vendor # 2 is blank, the system or vendor admin does not promote a vendor 3 to vendor 2 if vendor 2 is blank for some valid reason?

What is the logic behind having alternate vendors? Pricing? Stock availability? Other?

Does the company policy allow the system to have a vendor 3 when vendor 2 is blank?

Why would vendor 1 be blank? when there is vendor 2 and vendor 3?

The only reason each vendor is in a different row is because it is a different company and not the same company in a different location/store

"I have 3 columns for Vendors i.e Vendor 1, Vendor 2, Vendor 3. I want to put up a formula in "Vendor Master" such that IF "Vendor 1" is blank then it should return value from "Vendor 2" in "Master Vendor"." - Gives me the impression that the vendor is the same company in a different location.

What is the basis on the condition that there is "no vendor"?

Forum Discussion

IF statement based on multiple columns.

Resources