Power Query Multiple if contains

[bdenn]

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

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"

 

[johnny51981]

Can you provide an example of what the actual data looks like in both the SITE and NUMBER columns?

 

[Kerryx]

Don't forger PQ is case sensitive so you will need to account for East or east of fix in your source data

= 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")

 

[bdenn]

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:

SITE	NUMBER	CLEAN
WEST-First	1	West1
WEST-Second	1	West1
East-First	1	East1
East-Second	1	East1

Thanks,
Bdenn

 

[johnny51981]

Give this a shot:

= Table.AddColumn([PREVIOUS STEP], "CLEAN", each Text.Proper(Text.BeforeDelimiter([SITE],"-"))&Number.ToText([NUMBER]))

 

[bdenn]

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

Site	Type	Clean
(SEP 012 2022) 0200 East.csv	High	SiteA Status Now
(SEP 012 2022) 0200 East.csv	Medium	SiteA Status Soon
(SEP 012 2022) 0200 East.csv	Low	SiteA Status Wait
(SEP 012 2022) 0200 West.csv	High	SiteB Status Now
(SEP 012 2022) 0200 West.csv	Medium	SiteB Status Soon
(SEP 012 2022) 0200 West.csv	Low	SiteB Status Wait
(SEP 012 2022) 0200 North.csv	High	SiteC Status Now
(SEP 012 2022) 0200 North.csv	Medium	SiteC Status Soon
(SEP 012 2022) 0200 North.csv	Low	SiteC Status Wait
(SEP 012 2022) 0200 South.csv	High	SiteD Status Now
(SEP 012 2022) 0200 South.csv	Medium	SiteD Status Soon
(SEP 012 2022) 0200 South.csv	Low	SiteD Status Wait

 

[Kerryx]

For the original query did not know there were text strings or delimiters involved so adjusted as follows

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"

 

[Kerryx]

For the second part i added a helper column to convert you High, med & low to Now, Soon Wait just keeps final formula simpler

Helper first a conditional column

and your Clean Column formula as follows

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]

 

[bdenn]

For the original query did not know there were text strings or delimiters involved so adjusted as follows

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

 

[Kerryx]

Glad to help