Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RICHARDCHIANG1
Frequent Visitor

Excel Power Query - Stop query if record field does not exist

0

I'm running a query which returns a record with a number of fields. If the record contains the field 'CashField', the query continues as normal. However, if the field does NOT exist, is there a way to terminate the query cleanly and exit the function i.e. not subsequent steps from running?

 

let
    Source = Excel.CurrentWorkbook(){[Name="CASH_FLOW_QUERY"]}[Content],
    ...
    res = Record.HasFields(Source, "CashField"),
in
    res
1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

@RICHARDCHIANG1 your Source does not look like a record... Anyway, _after in_ instead of res write smth like

if Record.HasFields(your_test_record, "CashField") then name_of_the_last_step else [Ted_Striker_Says = "what a pisser"]. Power Query M is "lazy" so it starts from the end and won't execute subsequent steps if this field is missing. 

View solution in original post

3 REPLIES 3
AlienSx
Super User
Super User

@RICHARDCHIANG1 your Source does not look like a record... Anyway, _after in_ instead of res write smth like

if Record.HasFields(your_test_record, "CashField") then name_of_the_last_step else [Ted_Striker_Says = "what a pisser"]. Power Query M is "lazy" so it starts from the end and won't execute subsequent steps if this field is missing. 

I see, thank you! Before, i was trying something like: if Record.HasFields(your_test_record, "CashField")  = False then <EXIT_QUERY>  inside the LET statement... Sounds like my understanding of Power Query M was wrong! 

@RICHARDCHIANG1 order of lines of your code does not matter

let
    res = if b = 2 then null else c,
    a = 1, 
    c = a + b, 
    b = 2
in
    res

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors