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

Query with multiple "let" in a "try" with "otherwise"

I am trying to have a query that runs one "let" statment and if that one fails it trys(otherwise) another. Is that possible?

example:

try

let

Source = SharePoint.Files("https://location1", [ApiVersion = 15])

.....

in #"LastStepInLet1"

 

otherwise

 

let

Source = SharePoint.Files("https://location2", [ApiVersion = 15])

.....

in #"LastStepInLet2"

 

1 ACCEPTED SOLUTION
_AlexandreRM_
Helper II
Helper II

It seems like you are using a variable as query source. Try to:

Open Power Query options,

Open confidentiality settings in Global or Current file section,

Check the last box saying something like "Always ignore"

_AlexandreRM__0-1666885167614.png

 

Then refresh your query. If the data source was the only problem it should work.

 

Your problem here is that Power Query needs to check confidentiality of source files to determine the security of the query. If you are using a variable source file (=which can be changed programatically by PQ), PQ can't perform these checks. So, instead of risking to execute an unsecured query, it shows up this error.

The steps I provided above tells PQ to ignore these checks, which allows it to execute the query normally.

View solution in original post

9 REPLIES 9
_AlexandreRM_
Helper II
Helper II

It seems like you are using a variable as query source. Try to:

Open Power Query options,

Open confidentiality settings in Global or Current file section,

Check the last box saying something like "Always ignore"

_AlexandreRM__0-1666885167614.png

 

Then refresh your query. If the data source was the only problem it should work.

 

Your problem here is that Power Query needs to check confidentiality of source files to determine the security of the query. If you are using a variable source file (=which can be changed programatically by PQ), PQ can't perform these checks. So, instead of risking to execute an unsecured query, it shows up this error.

The steps I provided above tells PQ to ignore these checks, which allows it to execute the query normally.

It worked, thanks! the issue was 2 fold. You help get the "let" fixed and the confidentiality settings.

_AlexandreRM_
Helper II
Helper II

It's absolutely doable. The try/otherwise keywords enable you to create "blocks" of code, which you can manage as a single instruction (that's because the let/in instructions always send back a single value, which can be a string, a number, a table, or whatever).

 

So, your code structure is absolutely correct. However, the code from your example could be simplified as follows (assuming all other instructions on location1 or location2 will be the same):

Source = try SharePoint.Files("https://location1", [ApiVersion = 15]) otherwise SharePoint.Files("https://location2", [ApiVersion = 15])

 

I did try that but I should have added a bit more contrext. I am trying to have it handel more then just the location change. Sometimes the source which is out of my control with have sheet name changes, so I am having it return a dummy data set if that happen.

I need it to do multiple step within the try if the first source fails. The following is what I tried but it didnt work.

 

let

   Source = SharePoint.Files("https://location1", [ApiVersion = 15]),
   #"DataChecker" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Data Check")),
   #"UrlFilter" =
     try
       Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-IG-URL")
     otherwise
       Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-Wrong-URL"),
   #"FileNameFilter" =
     try
       Table.SelectRows(#"UrlFilter", each Text.Contains([Name], #"vp-IG-FilenameClosed"))
     otherwise
       Table.SelectRows(#"UrlFilter", each Text.Contains([Name], #"vp-Wrong-Filename")),
   File =
     try
       #"FileNameFilter"{[Name= vFileName ,#"Folder Path"= #"vp-IG-URL"]}[Content]
     otherwise
       #"FileNameFilter"{[Name= vFileName ,#"Folder Path"= #"vp-Wrong-URL"]}[Content],
   #"Imported Excel Workbook" = Excel.Workbook(File),
   Complaints_Sheet = #"Imported Excel Workbook"{[Item="Complaints",Kind="Sheet"]}[Data]
in
Complaints_Sheet

 

I also tired 

 

let 
    Source = SharePoint.Files("https://location1", [ApiVersion = 15]),
    #"DataChecker" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Data Check")),
    
    try
       #"UrlFilter" = Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-IG-URL"),
       #"FileNameFilter" = Table.SelectRows(#"UrlFilter", each Text.Contains([Name], #"vp-IG-FilenameClosed")),
       File = #"FileNameFilter"{[Name= vFileName ,#"Folder Path"= #"vp-IG-URL"]}[Content],

    otherwise 
       #"UrlFilter" = Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-Wrong-URL"),
       #"FileNameFilter" = Table.SelectRows(#"UrlFilter", each Text.Contains([Name], #"vp-Wrong-Filename")),
       File = #"FileNameFilter"{[Name= vFileName ,#"Folder Path"= #"vp-Wrong-URL"]}[Content],
            
    #"Imported Excel Workbook" = Excel.Workbook(File),
    Complaints_Sheet = #"Imported Excel Workbook"{[Item="Complaints",Kind="Sheet"]}[Data]
in
Complaints_Sheet

 

 

both didnt work, thoughts?

Thanks!

What is your error exactly? Could you screenshot it?

 

I don't understand exactly what you want to achieve. From my point of view the instruction

   #"UrlFilter" =
     try
       Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-IG-URL")
     otherwise
       Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-Wrong-URL"),

could be converted to

   #"UrlFilter" = Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-IG-URL" or [Folder path] = #"vp-Wrong-URL")

 I don't see why you used error handling here.

 

Also on your 2nd query, you need to store the result of your try/otherwise in a variable. For example:

mySuperVariable = try let ... in ... otherwise let ... in ...,

Thanks for your response. I'm not as concerned about the file path error checking (the "try" you recommended fixed that) as I am the following. 

 

1. If there is no file (in the first "let") use the dummy data file (the second "let" )

2. If the file(in the first "let") has the wrong sheet name use the dummy data file (the second "let" )

3. If everything is good use the new file(in the first "let") 

 

Just tried the follow and got the following error.

 

let 
  vWhichOneWorks = 
   try
    let
     Source = SharePoint.Files("https://location1", [ApiVersion = 15]),
     #"DataChecker" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Data Check")),
     #"UrlFilter" = Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-IG-URL"),
     #"FileNameFilter" = Table.SelectRows(#"UrlFilter", each Text.Contains([Name], #"vp-IG-FilenameClosed")),
     File = #"FileNameFilter"{[Name= vFileName ,#"Folder Path"= #"vp-IG-URL"]}[Content]
    in
     File
    otherwise
     let
      Source = SharePoint.Files("https://usaf.dps.mil/teams/U-AiRAD/", [ApiVersion = 15]),
      #"DataChecker" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Data Check")),
      #"UrlFilter" = Table.SelectRows(#"DataChecker", each [Folder Path] = #"vp-Wrong-URL"),
      #"FileNameFilter" = Table.SelectRows(#"UrlFilter", each Text.Contains([Name], #"vp-Wrong-Filename")),
      File = #"FileNameFilter"{[Name= vFileName ,#"Folder Path"= #"vp-Wrong-URL"]}[Content]
     in
     File,

    Working_Source = vWhichOneWorks,
    #"Imported Excel Workbook" = Excel.Workbook(Working_Source),
    Complaints_Sheet = #"Imported Excel Workbook"{[Item="Complaints",Kind="Sheet"]}[Data]
in
Complaints_Sheet

 I get this error no matter which step I click onI get this error no matter which step I click on

See my message below

AntrikshSharma
Community Champion
Community Champion

@BeardyMcBeard What do you mean by "is that possible"? Did you try it?

I did try it but it errored out.

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.

Top Solution Authors
Top Kudoed Authors