Forum Discussion
[Advanced Editor] Multiple if statement in M Script
Hello everyone,
is it possible to create multiple if statement? I am asking here because I couldn't find any normal tutorial for this. If I use the code like this I am getting error "Token Identifier expected.", I am trying to create historical data for dataset for past 5 days.
if
Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 0
then
(let
Source = Oracle.Database("LINK", [HierarchicalNavigation=true]),
NAME = Source{[Schema="NAME"]}[Data],
RENAME = NAME{[Name="DATASET"]}[Data],
ConvertedPath = Text.Replace("PATHFORFILE", "\", "/"),
RScript = R.Execute("write.csv(NAME,"""&ConvertedPath&""")
output <- NAME
",[NAME=NAME]),
output = RScript{[Name="FILENAME"]}[Value]
in
output)
else
(let
Source = Csv.Document(File.Contents("PATHOFFILE.csv"),[Delimiter=",", Columns=, Encoding=, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{COLUMNS}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{COLUMNS}),
/*IF AGAIN*/
if
Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 1
then
(let
Source = Oracle.Database("LINK", [HierarchicalNavigation=true]),
NAME = Source{[Schema="NAME"]}[Data],
RENAME = NAME{[Name="DATASET"]}[Data],
ConvertedPath = Text.Replace("PATHFORFILE", "\", "/"),
RScript = R.Execute("write.csv(NAME,"""&ConvertedPath&""")
output <- NAME
",[NAME=NAME]),
output = RScript{[Name="FILENAME"]}[Value]
in
output)
else
(let
Source = Csv.Document(File.Contents("PATHOFFILE.csv"),[Delimiter=",", Columns=, Encoding=, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{COLUMNS}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{COLUMNS}),
/*IF AGAIN*/
Well, you can have multiple if statements as in the query below.
I included a "let .. in" statement for Monday, just to illustrate how that would look like.
let Output = if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 0 then let InnerOutput = "Monday" in InnerOutput else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 1 then "Tuesday" else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 2 then "Wednesday" else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 3 then "Thursday" else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 4 then "Thank God it's Friday" else "Weekend!!" in OutputIn general the structure of a query is like:
let step1 = formula, step2 = formula, ... stepn = formula in stepnIt is not possible to create an if ... then ... else statement without a step name.
1 Reply
Well, you can have multiple if statements as in the query below.
I included a "let .. in" statement for Monday, just to illustrate how that would look like.
let Output = if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 0 then let InnerOutput = "Monday" in InnerOutput else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 1 then "Tuesday" else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 2 then "Wednesday" else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 3 then "Thursday" else if Date.DayOfWeek(DateTime.LocalNow(), Day.Monday) = 4 then "Thank God it's Friday" else "Weekend!!" in OutputIn general the structure of a query is like:
let step1 = formula, step2 = formula, ... stepn = formula in stepnIt is not possible to create an if ... then ... else statement without a step name.