Conditional Code Branching

Power Query
1

Hi

I am sourcing data from a named range in a Excel workbook. However i need to handle the situation where the named range does not exist in the source workbook.

I believe I could use try/otherwise -however I am not sure of how to do this where if it true then do these steps otherwise do these steps. Perhaps one of the steps needs to be a Error Alert of some kind so the user knows that the named range doesn’t exist.

Or is there a simpler way?

Thanks for your help

Allister
CBranch.xlsx (16.6 KB)
r

2

Hi @AllisterB,

You can introduce conditional logic with an if-then-else statement.
For example, replace your code with this OR copy this code into a new blank query

if Excel.CurrentWorkbook(){[Name="Table1"]}? =null

then 
    "NamedRange doesn't exist"

else 
    let
        Data = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Multiplied Column" = Table.TransformColumns(Data, {{"Age", each _ * 10, type number}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Multiplied Column",{"Name", "Town", "Age"})
    in
        #"Reordered Columns"

I hope this is helpful

3

Thank You Melissa :slight_smile:
Would this work for a named Range in a workbook that is not the Current workbook?

4

Your source would be different of course but the method can be applied regardless.

I hope this is helpful

5

Hi Melissa

I have tried to use the advice - see below. However I get an error (see below for eth error ad my code.

I trust that you will be able to spot my error. I would like to be able to send a full mock-up but I don’t know how to attach several Excel files to a posting so that external references in a query can work for people looking at the posting. Could you send me a link to instructions as to how to do this.

In the meantime I hope it is easy to spot the error.

The error is
An error occurred in the ‘’ query. Expression.Error: We cannot convert the value “Final Branch Model …” to type Table.
Details:
Value=Final Branch Model _LY
Type=[Type]

// Final Branch Model _LY - refers to a Query with the following Code
//let
// Source = Excel.Workbook(File.Contents(pe_FilePath_LY_Workbook), null, true),
// #“Sorted Rows” = Table.Sort(Source,{{“Name”, Order.Ascending}})
//in
// #“Sorted Rows”

My current Code

// pe_FilePath_LY_Workbook is a parameter that results in a Path and Excel File name with xlsx extension

// r_xLoansReceivable is a Named Range in workbook used by the paramter query pe_FilePath_LY_Workbook

if Table.SelectRows(“Final Branch Model _LY”, each Text.Contains([Name], “r_xLoansReceivable”)) =null

then
“NamedRange does not exist”

else

let
InputSource = “r_xLoansReceivable”,
Source = #“Final Branch Model _LY”,
#“Filtered Rows” = Table.SelectRows(Source, each Text.Contains([Name], “r_xLoansReceivable”))

in
#“Filtered Rows”

6

@AllisterB in my previous response I illustrated that you first need to test for the presence of the table. That would look something like below.

if Final Branch Model _LY{[Name="r_xLoansReceivable"]}? =null
.

This chapter below will aid you in resolving errors when implementing code.

But I sincerely hope you’ll enjoy the entire course…

1 Like
7

Hi Melissa

Thank you for your expert advice and helpful responses. The chapters are great are too

Allister