none
Check if sheet exists before executing the Power Query RRS feed

  • Question

  • Hi,

    I have the sheet name entered as parameter, depending on a cell in Excel. I'd like to prevent Power Query from getting an error if the entered sheet doesn't exist. Any possibility to insert in the query a "if sheet X exists then keep going else stop" ?

    Thanks.

    Thursday, February 20, 2020 8:17 AM

Answers

  • I am assuming he/she knows how to do the parameter, and then I focused only on the request which is:
    Any possibility to insert in the query a "if sheet X exists then keep going else stop" ?

    1. is working and is providing a syntax that is working

    let
    Source = Table.SelectRows(
    	Folder.Contents("W:\...\XXXX\"),  //#"path"
    	each 
    		try [Name]="2020-01_01.csv" //"filename"
    		otherwise false
    ),
    test = 	if Table.RowCount(Source)=0
    	then "no file"		//#"DO WHAT YOU WANT WHEN FILE DOES NOT EXIST" 
    	else "file exists"	//#"DO WHAT YOU WANT WHEN FILE EXISTS" 
    
    in test


    Regarding 2/, you are right, here a correct version:

    let
    Source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],   //"table1" is your sheet
    test = 
    	if (try Source) [HasError] 
    	then (try Source) [Error] [Message]  
    	else "sheet exists"
    in test


    edited on 02-Mar-2020:
    here is a function that checks whether a given file exists, it returns "true" if the file exists and "false" if the file does not exist

    (full_path as text, filename as text) as logical => let
    	path_2 = if Text.End(full_path,1)="\" then full_path else (full_path & "\"),
    	Source = Binary.Length( File.Contents(path_2 & filename) ),		//Binary.Length instead of Binary.Buffer to keep it small and not use the buffer
    	_error =  (try Source) [Error] [Message]						//only for debugging
    	in if (try Source) [HasError]	then false	else true

    Friday, February 21, 2020 10:59 AM

All replies

  • 1/ if your sheet is an external sheet, you can try this

    let
    	Filter = Table.SelectRows(
    		Folder.Contents("YOUR FOLDER"),
    		each 
    			try [Name]="YOUR FILENAME" 
    			otherwise false
    	)
    in
    	if Table.RowCount(Filter)=0
    	then #"DO WHAT YOU WANT WHEN FILE DOSE NOT EXIST" 
    	else #"DO WHAT YOU WANT WHEN FILE EXISTS" 


    2/ if your sheet is an internal sheet, you can try this

    let
        Source = try Excel.CurrentWorkbook(){[Name="YOUR SHEET NAME"]}[Content] otherwise false
    
    in 
    	if not Source
    	then #"DO WHAT YOU WANT WHEN YOUR SHEET DOES NOT EXIST" 
    	else #"DO WHAT YOU WANT WHEN YOUR FILE EXISTS"

    • Edited by anthony34 Thursday, February 20, 2020 9:06 AM
    • Proposed as answer by Lz._ Thursday, February 20, 2020 1:14 PM
    • Unproposed as answer by Lz._ Thursday, February 20, 2020 1:21 PM
    Thursday, February 20, 2020 8:58 AM
  • Hi Anthony

    Either I misunderstood something or your proposals won't work

    Friday, February 21, 2020 8:42 AM
  • which one ?
    Friday, February 21, 2020 8:59 AM
  • IMHO both:

    #1 Checks if a file/workbook exists, not a sheet name within that file
    #Excel.CurrentWorkbook returns the Tables - only - in the current workbook

    Galven6I have the sheet name entered as parameter, depending on a cell in Excel. I'd like to prevent Power Query from getting an error if the entered sheet doesn't exist

    We haven't heard from Galven so maybe your proposals work if with "sheet name" s/he actually wanted to say file/workbook name

    Makes sense or am I really tired today?

    Friday, February 21, 2020 9:31 AM
  • I am assuming he/she knows how to do the parameter, and then I focused only on the request which is:
    Any possibility to insert in the query a "if sheet X exists then keep going else stop" ?

    1. is working and is providing a syntax that is working

    let
    Source = Table.SelectRows(
    	Folder.Contents("W:\...\XXXX\"),  //#"path"
    	each 
    		try [Name]="2020-01_01.csv" //"filename"
    		otherwise false
    ),
    test = 	if Table.RowCount(Source)=0
    	then "no file"		//#"DO WHAT YOU WANT WHEN FILE DOES NOT EXIST" 
    	else "file exists"	//#"DO WHAT YOU WANT WHEN FILE EXISTS" 
    
    in test


    Regarding 2/, you are right, here a correct version:

    let
    Source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],   //"table1" is your sheet
    test = 
    	if (try Source) [HasError] 
    	then (try Source) [Error] [Message]  
    	else "sheet exists"
    in test


    edited on 02-Mar-2020:
    here is a function that checks whether a given file exists, it returns "true" if the file exists and "false" if the file does not exist

    (full_path as text, filename as text) as logical => let
    	path_2 = if Text.End(full_path,1)="\" then full_path else (full_path & "\"),
    	Source = Binary.Length( File.Contents(path_2 & filename) ),		//Binary.Length instead of Binary.Buffer to keep it small and not use the buffer
    	_error =  (try Source) [Error] [Message]						//only for debugging
    	in if (try Source) [HasError]	then false	else true

    Friday, February 21, 2020 10:59 AM
  • I can suggest reading Ben Gribaudo:

    Power Query M Primer (Part 15): Error Handling

    Friday, February 21, 2020 11:14 AM
  • I focused only on the request which is:
    Any possibility to insert in the query a "if sheet X exists then keep going else stop" ?

    OK I see (+ else "Table exists" :) & Yes Ben's pages are definitively worth reading
    Friday, February 21, 2020 12:28 PM
  • Thursday, February 27, 2020 9:45 AM