Power Query Multiple if contains

bdenn

New Member
Joined
Feb 3, 2017
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello,
I'm using power query to compile multiple spreadsheets. I have added a custom column which I need to have an If/else statement.

If my SITE column contains East and in the same row the NUMBER column contain 1 then enter "East1" This part is working but I have more data to check, else if SITE contain West and NUMBER contains 1 then enter WEST1.

This is just sample data, I have about 20 if's to compile. How to I make this work?

Thanks,
Bdenn

Power Query:
if Text.Contains([SITE],"East") 
and 
Text.Contains([NUMBER],"1") = true then "East1"
else
if Text.Contains([SITE],"West") 
and 
Text.Contains([NUMBER],"1") = true then "West1"
else
"NO"
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Don't forger PQ is case sensitive so you will need to account for East or east of fix in your source data
Power Query:
= Table.AddColumn(#"Renamed Columns1", "Custom", each if[SITE]="East" and [NUMBER]=1 then
 [SITE] & Text.From([NUMBER]) else if[SITE]="West" and[NUMBER]=1 then[SITE] & Text.From ([NUMBER]) else "no")
 
Upvote 0
Hello,
I tied to adapt the code above and it did not work for me.

This is just example data but I would be looking for an solution which we enter "East1" and not pull the data from each cell as the east and west are not clean in there column.

Example of how how the data would look:

SITENUMBERCLEAN
WEST-First1West1
WEST-Second1West1
East-First1East1
East-Second1East1

Thanks,
Bdenn
 
Upvote 0
Give this a shot:

= Table.AddColumn([PREVIOUS STEP], "CLEAN", each Text.Proper(Text.BeforeDelimiter([SITE],"-"))&Number.ToText([NUMBER]))
 
Upvote 0
Hello,
I did not have any luck with the above formulas.

I need to take information from 2 different cells and if both are true then enter in the CLEAN else check the next.

There are 7 spreadsheets and I will need to enter in each of the else's to accommodate them all.

If the Site Contains "East" and the Type is "High" in Column Clean display "SiteA Status Now" / If Site Contains "West" and Type is "Low" Display Clean as "SiteB Status Wait"

Thanks,
Bdenn

SiteTypeClean
(SEP 012 2022) 0200 East.csvHighSiteA Status Now
(SEP 012 2022) 0200 East.csvMediumSiteA Status Soon
(SEP 012 2022) 0200 East.csvLowSiteA Status Wait
(SEP 012 2022) 0200 West.csvHighSiteB Status Now
(SEP 012 2022) 0200 West.csvMediumSiteB Status Soon
(SEP 012 2022) 0200 West.csvLowSiteB Status Wait
(SEP 012 2022) 0200 North.csvHighSiteC Status Now
(SEP 012 2022) 0200 North.csvMediumSiteC Status Soon
(SEP 012 2022) 0200 North.csvLowSiteC Status Wait
(SEP 012 2022) 0200 South.csvHighSiteD Status Now
(SEP 012 2022) 0200 South.csvMediumSiteD Status Soon
(SEP 012 2022) 0200 South.csvLowSiteD Status Wait
 
Upvote 0
For the original query did not know there were text strings or delimiters involved so adjusted as follows
Power Query:
if Text.Contains([SITE],"East") and [NUMBER]=1 then Text.BeforeDelimiter ([SITE],"-") & Text.From([NUMBER]) else if Text.Contains([SITE],"WEST") and [NUMBER]=1 then Text.BeforeDelimiter ([SITE],"-") & Text.From ([NUMBER]) else "No"
1662102370272.png
 
Upvote 0
Solution
For the second part i added a helper column to convert you High, med & low to Now, Soon Wait just keeps final formula simpler
1662103910650.png


Helper first a conditional column
1662103986786.png

and your Clean Column formula as follows
Power Query:
if Text.Contains([Site],"East") then "SiteA Status " &[Custom] else if Text.Contains([Site],"West") then "SiteB Status " &[Custom] else if Text.Contains([Site],"North") then "SiteC Status " &[Custom] else  "SiteC Status " &[Custom]
 
Upvote 0
For the original query did not know there were text strings or delimiters involved so adjusted as follows
Power Query:
if Text.Contains([SITE],"East") and [NUMBER]=1 then Text.BeforeDelimiter ([SITE],"-") & Text.From([NUMBER]) else if Text.Contains([SITE],"WEST") and [NUMBER]=1 then Text.BeforeDelimiter ([SITE],"-") & Text.From ([NUMBER]) else "No"
View attachment 73065

Hello,
I have marked this post as the correct solution!

I was able to make this work correctly for me. Your other option for adding a column I did not try as this solved it.

Thanks,
Bdenn
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top