Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to Solution.
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"
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 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"
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.
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
See my message below
I did try it but it errored out.