Multiple lines in IF statement

ptamas88 · Sep 13, 2016

Hi everybody!

I have looked all over the internet, and found chunks of information, but havent had success in using these infos in my querys.
So my problem is:
1. I have 3 Querys which do almost the same thing, but with little differences.
2. I wanna sum these 3 queries up in one query with an IF statement.
3. The IF statement gets the data from the first command of the query, and after that command the IF statement comes in the second command.
4. The rest of the code should be in the IF statement.
Here is my code:

Code:

let
    Source = Folder.Files("..."),
    First command = Table.AddColumn(Source, "Service Provider", each if List.Count(List.FindText(Text.Split([Folder Path],"\"),"Vodafone")) >0 then "Vodafone" else if List.Count(List.FindText(Text.Split([Folder Path],"\"),"T-Mobile")) >0 then "T-Mobile" else ""),
    The IF command = if [Service Provider] = "Vodafone" then /[B]/here should come the magic
    //the following commands should be in the true branch of the IF statement[/B]
       FILTER files = Table.SelectRows(The IF command, each Text.Contains([Name], "...") and ([Extension] = ".csv")),
       ADD COLUMN1 = Table.AddColumn(FILTER files, "Date", each Date.FromText(Text.Start(Text.End([Folder Path],7),4)&"."&Text.Start(Text.End([Folder Path],3),2)&".01"), type date),
       ADD COLUMN2 = Table.AddColumn(ADD COLUMN1", "Year", each Date.Year([Date])),
in
    ADD COLUMN2
[B]//here comes the else branch
    [/B]else 
       ADD COLUMN1 = Table.AddColumn(FILTER files, "Date", each Date.FromText(Text.Start(Text.End([Folder Path],7),4)&"."&Text.Start(Text.End([Folder Path],3),2)&".01"), type date),
       ADD COLUMN2 = Table.AddColumn(ADD COLUMN1", "Year", each Date.Year([Date])),
in
   ADD COLUMN2

Can you help me in this situation? What is the proper syntax?

Dan Bliss · May 26, 2017

Hello ptama88,

I have questions...

1st command: would the simpler search for "Vodafone" string anywhere in [Folder Path] produce the result you want, rather than splitting [Folder Path]?
Not sure if I should take your "..." search literally: Do you really have file names with "..." (3 consecutive dots) in them?
Can you provide an example of Folder Path text which shows what data is before #Add Column1" & #"Add Column2", and expected results from each step.

Here is my sample data. Please revise in your sample.

Name	Extension	Date accessed	Date modified	Date created	Folder Path
vodafone2015may.csv	.csv	5/20/2017 10:06	5/20/2017 10:06	5/19/2017 8:08	C:\Users\ptamas\Vodafone
2015may.csv	.csv	3/12/2017 11:25	3/12/2017 11:25	3/11/2017 21:12	D:\T-Mobile
tmobile2015may.txt	.txt	5/17/2017 7:08	5/17/2017 7:08	5/17/2017 7:08	C:\Users\emile\Tmobile
2015may.xls	.xls	2/8/2017 9:22	2/8/2017 9:23	2/8/2017 9:23	F:\Vodafonestuff
anon….csv	.csv	2/8/2017 9:22	2/8/2017 9:23	2/8/2017 9:23	C:\Users\ptamas\Vodafone

Code:

let    
     Source = Excel.CurrentWorkbook(){[Name="phone"]}[Content],

// Always important to type columns - don't leave as ANY
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Extension", type text}, {"Date accessed", type datetime}, {"Date modified", type datetime}, {"Date created", type datetime}, {"Folder Path", type text}}),

//I added a step so case wouldn't cause search to fail
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Folder Path", "Folder PathUC"),
    #"Uppercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Folder PathUC", Text.Upper}}),

//Here's your first IF
    #"Added Conditional Column" = Table.AddColumn(#"Uppercased Text", "Service Provider", each if Text.Contains([Folder PathUC], "VODAFONE") then "VODAPHONE" else if Text.Contains([Folder PathUC], "T-MOBILE") then "T-MOBILE" else null ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Service Provider", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Extension] = ".csv")),

//"..." what is this? 
    #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows", "has...", each if Text.Contains([Name], "...") then "TRUE" else "FALSE" )
in
    #"Added Conditional Column1"

Multiple lines in IF statement

ptamas88

New member

Dan Bliss

New member