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
DominikW
Frequent Visitor

[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*/

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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
    Output

 

In general the structure of a query is like:

 

let
    step1 = formula,
    step2 = formula,
    ...
    stepn = formula
in
    stepn

It is not possible to create an if ... then ... else statement without a step name.

 

Specializing in Power Query Formula Language (M)

View solution in original post

1 REPLY 1
MarcelBeug
Community Champion
Community Champion

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
    Output

 

In general the structure of a query is like:

 

let
    step1 = formula,
    step2 = formula,
    ...
    stepn = formula
in
    stepn

It is not possible to create an if ... then ... else statement without a step name.

 

Specializing in Power Query Formula Language (M)

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.