If else statement in Power Query throws error "Token Else expected"

Question

I am pretty new to Power Query

I have two separate Power Querys that work in separate/stand-alone queries, but I want to use an if statement that that takes the value of a cell with a checkbox to use either the JSON or the CSV

I have a checkbox in a cell called useWhat then two other cells one called SourceLink which has a link to a JSON file and another called fp which is a local link to a folder with a CSV in it

I have the following statement, but I am getting an error right after the
SourceLink = Excel.CurrentWorkbook(){[Name="SourceLink"]}[Content]{0}[Column1],

the comma is highlighted in red.

Any assistance with this If statement is appreciated

Thanks

essentially I am trying to do this

let
   #what = 
      if useWhat = "TRUE" then 
         import via JSON
      else
         import via CSV
      end if
      more code

what I have

let 
    #"what" = 
        if useWhat = "TRUE" then 
           SourceLink = Excel.CurrentWorkbook(){[Name="sourceLink"]}[Content]{0}[Column1],
           Source = Json.Document(Web.Contents(sourceLink)),
           Expanded=Table.FromRecords (Source[elements] ),
           #"Removed Errors" = Table.RemoveRowsWithErrors(Expanded),
           #"Added Index" = Table.AddIndexColumn(#"Removed Errors", "Index", 0, 1, Int64.Type),
           #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
           #"Combine items in list" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", each try if Value.Is(_, type list ) then Text.Combine(_,"|") else _ otherwise _, type text}}),
           #"Pivoted Column" = Table.Pivot(#"Combine items in list", List.Distinct(#"Combine items in list"[Attribute]), "Attribute", "Value"),
           #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
           #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Table.ColumnNames(#"Removed Columns"),each {_,type text}))
        else
           FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath],
           Source = Csv.Document(File.Contents(FilePath),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
           #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}}),
           #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",List.Transform(Table.ColumnNames(#"Promoted Headers"),each {_,type text}))
        end if

    #"Appended Query" = Table.Combine({#"Changed Type", addMissingEmails}),
    firstColumns = {"Id", "Label", "Email", "Project Name", "Type", "Segment", "Description", "First Name", "Last Name", "Email 2", "Email 3", "Initial Date", "Last Date", "Image"},
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",firstColumns & List.RemoveMatchingItems(Table.ColumnNames(#"Appended Query"),firstColumns),MissingField.Ignore),
    #"Demoted Headers" = Table.DemoteHeaders(#"Reordered Columns"),
    BulkReplaceStep = fBulkReplace(#"Demoted Headers", MyFindReplace, Table.ColumnNames(#"Demoted Headers")),
    #"Replaced Value" = Table.ReplaceValue(BulkReplaceStep,"#(00A0)","",Replacer.ReplaceText,Table.ColumnNames(BulkReplaceStep)),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(0087)","",Replacer.ReplaceText,Table.ColumnNames(#"Replaced Value")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value1", [PromoteAllScalars=true]),
    #"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Label", Order.Ascending}}),
    #"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Id", Text.Trim, type text}}),
    newMergedHeader = Table.ColumnNames(mergeHeaders){0},
    
    #"Merged Columns" = 
        if newMergedHeader <> null then 
          Table.RenameColumns(
          Table.CombineColumns(#"Trimmed Text",Table.Column(mergeHeaders,newMergedHeader),each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.Difference(List.RemoveNulls(_), {""})),"Merged"),{{"Merged",newMergedHeader}})
        else #"Trimmed Text"
in
    #"Merged Columns"

how does PQ know when the else if at the end of its statement? — xyz333
– xyz333, Commented Aug 27, 2022 at 17:56
I'm not sure what you mean but that is not how to write an if statement in M. Additionally, your #"Appended Query" line will probably not work as it isn't referencing a step. You probably need to look at let/in and read some PQ tutorials. I suggest starting here: bengribaudo.com/blog/2017/11/17/4107/… — davidebacci
– davidebacci, Commented Aug 27, 2022 at 17:58
the query works just fine for either the sourceLink = Excel.CurrentWorkbook(){[Name="SourceLink"]}[Content]{0}[Column1] or the FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath] I just can not figure out the if statement — xyz333
– xyz333, Commented Aug 27, 2022 at 18:01

davidebacci · Accepted Answer · 2022-08-27 18:48:07Z

2

I'm coding completely blind but you need something like the following:

let 
    #"what" = if useWhat = "TRUE" then a else b,
        
    a = let
           SourceLink = Excel.CurrentWorkbook(){[Name="sourceLink"]}[Content]{0}[Column1],
           Source = Json.Document(Web.Contents(sourceLink)),
           Expanded=Table.FromRecords (Source[elements] ),
           #"Removed Errors" = Table.RemoveRowsWithErrors(Expanded),
           #"Added Index" = Table.AddIndexColumn(#"Removed Errors", "Index", 0, 1, Int64.Type),
           #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
           #"Combine items in list" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", each try if Value.Is(_, type list ) then Text.Combine(_,"|") else _ otherwise _, type text}}),
           #"Pivoted Column" = Table.Pivot(#"Combine items in list", List.Distinct(#"Combine items in list"[Attribute]), "Attribute", "Value"),
           #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
           #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Table.ColumnNames(#"Removed Columns"),each {_,type text}))
        in #"Changed Type",
        
        b = let
        
            FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath],
           Source = Csv.Document(File.Contents(FilePath),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
           #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}}),
           #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
            #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",List.Transform(Table.ColumnNames(#"Promoted Headers"),each {_,type text}))
        in #"Changed Type2",

    #"Appended Query" = Table.Combine({#"what", addMissingEmails}),
    firstColumns = {"Id", "Label", "Email", "Project Name", "Type", "Segment", "Description", "First Name", "Last Name", "Email 2", "Email 3", "Initial Date", "Last Date", "Image"},
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",firstColumns & List.RemoveMatchingItems(Table.ColumnNames(#"Appended Query"),firstColumns),MissingField.Ignore),
    #"Demoted Headers" = Table.DemoteHeaders(#"Reordered Columns"),
    BulkReplaceStep = fBulkReplace(#"Demoted Headers", MyFindReplace, Table.ColumnNames(#"Demoted Headers")),
    #"Replaced Value" = Table.ReplaceValue(BulkReplaceStep,"#(00A0)","",Replacer.ReplaceText,Table.ColumnNames(BulkReplaceStep)),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(0087)","",Replacer.ReplaceText,Table.ColumnNames(#"Replaced Value")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value1", [PromoteAllScalars=true]),
    #"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Label", Order.Ascending}}),
    #"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Id", Text.Trim, type text}}),
    newMergedHeader = Table.ColumnNames(mergeHeaders){0},
    
    #"Merged Columns" = 
        if newMergedHeader <> null then 
          Table.RenameColumns(
          Table.CombineColumns(#"Trimmed Text",Table.Column(mergeHeaders,newMergedHeader),each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.Difference(List.RemoveNulls(_), {""})),"Merged"),{{"Merged",newMergedHeader}})
        else #"Trimmed Text"
in
    #"Merged Columns"

edited Aug 27, 2022 at 18:48

answered Aug 27, 2022 at 18:15

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

xyz333 Over a year ago

David, thank you. I added

#"useWhat"  = Excel.CurrentWorkbook(){[Name="useWhat1"]}[Content]{0}[Select CSV or JSON],     #"what" = if useWhat = "TRUE" then a else b,

to get the value of what. useWhat shows TRUE or FALSE correctly but the IF statement is always selecting b

horseyride Over a year ago

What does useWhat evaluate to ? The text string true or the value true ?

xyz333 Over a year ago

YES, that fixed it I used true without the quotes

xyz333 Over a year ago

David, I searched and tried for hours to get this. Thank you

davidebacci Over a year ago

Good stuff. Nice work

Collectives™ on Stack Overflow

If else statement in Power Query throws error "Token Else expected"

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related