Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multiple If Nested with TRIM

Hello Team,

 

I have a below formula which is giving error as "Cannot convert the value of type text to true/false", not sure what is going wrong, below is my formula and attached is my working sheet, Location filter is my output and SQIM Location normalized is my input. there is no option for attaching file 😞

 

Location Filter = IF ([SQIM Location normalized] = "OFS-DSC Jebel Ali" , "Dubai",
if([SQIM Location normalized] = "OFS-DSC Singapore", "Sing",
IF( [SQIM Location normalized] = "SC-DSC Logistics Houston" , "Hous",

IF ([SQIM Location normalized] = "Rott", "Rott",

IF ([SQIM Location normalized] = "KL Financial Hub Treasury" , "KLFH Treasury",
IF ([SQIM Location normalized] = "KL Financial Hub Tax" , "KLFH Tax",
IF ([SQIM Location normalized] = "KL Financial Hub General Acct" , "KLFH GA",
IF ([SQIM Location normalized] = "KL Financial Hub Shared" , "KLFH Shared",
IF ([SQIM Location normalized] = "GA" && [SQIM_Source]="KLFH Quest" , "KLFH GA",
if ([SQIM Location normalized] = "Tax" && [SQIM_Source]= "KLFH Quest" , "KLFH Tax",
if ([SQIM Location normalized] = "Treasury" && [SQIM_Source]= "KLFH Quest" , "KLFH Treasury",
IF ([SQIM Location normalized]= "OFS MEA HQ (Shared Folder)" , "KLFH Shared",
if ([SQIM Location normalized] = "GA" && [SQIM_Source]= "HFH Quest" , "HF GA",
if ([SQIM Location normalized] = "Tax" && [SQIM_Source]= "HFH Quest" , "HF Tax",
if ([SQIM Location normalized] = "Treasury" && [SQIM_Source]= "HFH Quest" , "HF Treasury",
if ([SQIM Location normalized] = "TAX" && [SQIM_Source]= "KLFH Quest" , "KLFH TAX",
if ([SQIM Location normalized] = "TAX" && [SQIM_Source]= "HFH Quest" , "HF TAX",
if ([SQIM Location normalized] = "SDM" && [SQIM_Source]= "HFH Quest" , "HF SDM",
if ([SQIM Location normalized] = "General" && [SQIM_Source] = "KLFH Quest" , "KLFH General",
if ([SQIM Location normalized] = "HFH" && [SQIM_Source]= "HFH Quest" , "KLFH General",
if ([SQIM Location normalized] = "Hub" && [SQIM_Function]= "Procurement" , "PnS Hub",
if ([SQIM Location normalized] = "HFH" && [SQIM_Source]= "HFH Quest" , "HFH Hub",
if ([SQIM Location normalized] = "KLFH GA" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH SDM" , [SQIM Location normalized],
if ([SQIM Location normalized] = "KLFH TAX" , [SQIM Location normalized],
IF( [SQIM Location normalized] ="KLFH Treasury" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KLFH Hub" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH Treasury" , [SQIM Location normalized],
if ([SQIM Location normalized] = "HFH Hub" , [SQIM Location normalized],
if ([SQIM Location normalized] ="HFH GA" , [SQIM Location normalized],
if ([SQIM Location normalized] ="HFH TAX" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KLFH Accenture" ,[SQIM Location normalized],
if ([SQIM Location normalized] ="HFH Accenture" , [SQIM Location normalized],
if ([SQIM Location normalized] ="KL Finance Hub General Acct" , "KLFH GA",
if ([SQIM Location normalized] ="KL Finance Hub Treasury","KLFH Treasury",
if ([SQIM Location normalized] ="KL Financel Hub Tax" , "KLFH TAX",
if ([SQIM Location normalized] = "PnS Hub" , "PnS Hub",
if ([SQIM_Area]= "NAR" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
if ([SQIM Location normalized]= "KL Finance Hub Tax" , "KLFH Tax",
if ([SQIM Location normalized]= "KLFH Shared" , "KLFH Shared",
if ([Geomarket (Code)]= "NGM" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
if ([Geomarket (Code)]= "NGM" && [SQIM_Area]= "NAR" && [SQIM_Function]= "Procurement" , [SQIM Location normalized],
IF( TRIM([SQIM Location normalized]) = "Houston Financial Hub Accenture (Mode 3)" && TRIM([SQIM_Source]) = "HFH Accenture" , "HFH Accenture",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Accenture (Mode 3)"  && TRIM([SQIM_Source]) = "KLFH Accenture" , "KLFH Accenture",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub General Acct" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH GA",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Shared" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH Shared",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Tax" && TRIM([SQIM_Source]) = "KLFH Quest" && "KLFH Tax",
IF (TRIM([SQIM Location normalized]) = "KL Finance Hub Treasury" && TRIM([SQIM_Source]) = "KLFH Quest" , "KLFH Treasury",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub ERM" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH ERM",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub FA\Inventory" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH FA\Inventory",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub General Acct" && TRIM([SQIM_Source]) = "TFH Quest" , "TFH GA",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub Tax"   && TRIM([SQIM_Source]) = "TFH Quest" , "TFH TAX",
IF (TRIM([SQIM Location normalized]) = "Tyumen Financial Hub Treasury"  && TRIM([SQIM_Source]) = "TFH Quest" , "TFH Treasury",
IF (TRIM([SQIM Location normalized]) = "Houston Financial Hub" && TRIM([SQIM_Source]) = "HFH Quest" , "HFH Hub",
IF ([SQIM Location normalized] = "Houston Financial Hub","HFH Hub",
if ([SQIM Location normalized] = " Houston Financial Hub ", "HFH Hub", "Field")
)))))))))))))))))))))))))))))))))))))))))))))))))))))))
2 ACCEPTED SOLUTIONS
Johanno
Responsive Resident
Responsive Resident

Hi,

 

I really think you should separate the formula using VAR and instead of IF using SWITCH. You would get a much cleaner formula.

 

Is the data type "True/False" instead of text?

 

/Johan

View solution in original post

v-yuta-msft
Community Support
Community Support

@Anonymous,

 

As the error message mentioned, you should check if there're column value type true/false. In addtion, to make your code more clear and readable, you can use switch instead of if function.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@Anonymous,

 

As the error message mentioned, you should check if there're column value type true/false. In addtion, to make your code more clear and readable, you can use switch instead of if function.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

All the column used are in Text data type only, I have changed to switch function still showing the same error. 😞

Johanno
Responsive Resident
Responsive Resident

Hi,

 

I really think you should separate the formula using VAR and instead of IF using SWITCH. You would get a much cleaner formula.

 

Is the data type "True/False" instead of text?

 

/Johan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.