Forum Discussion
Data Source Connection based on Parameters to switch between cloud and local sources possible?
- As it turns out, unfortunately, it looks like using an "if" statement to dictate the source within a query makes you lose the ability to refresh the dataset once published to the cloud.
Though everything works perfectly on the desktop, once the dataset is published, the service doesn't recognize the data source as being valid within the query, and therefore doesn't give you any options for refresh or editing credentials.
I tried writing the query your proposed way with the "if" statement outside of the source line as well as my method where the source line includes the if statement within it and both methods fail in the cloud.
So I think in reality what I want seems to be impossible.
Ah! I didn't realize I could have an "if" M statement to dynamically reference a source query.
To be sure I understand correctly - I can have 2 queries - 1 pointing to SharePoint called "SharePointSource" and another pointing to the local folder called "LocalSource" - and then I can have a reference query to dynamically point to the appropriate query based on the parameter?
Can you provide an example of an M "if" statement I could use to point to either "SharePointSource" or "LocalSource"?
Something like? I don't think that works.. but what would?
if Text.StartsWith("http", ConnectionParameter) then
let Source = SharePointSource
else
let Source = LocalSource
in
Source
Haven't fully, fully tested but the syntax should go something like this:
if Parameter1 = "Yes" then
let
Source = ...
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity", type text}, {"Customer", type text}, {"Phase", type text}, {"Owner", type text}, {"Division", type text}, {"Value", Currency.Type}, {"CloseDate", type date}, {"Status", type text}}),
#"Final" = #"Changed Type"
in
#"Final"
else
let
Source = ...
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity", type text}, {"Customer", type text}, {"Phase", type text}, {"Owner", type text}, {"Division", type text}, {"Value", Currency.Type}, {"CloseDate", type date}, {"Status", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Customer"}),
#"Final" = #"Changed Type"
in
#"Final"
Even simpler, instead of copying all syntax after source each time .. just doing what I proposed works - which is just applying the if then else statement to the source line alone and then letting the steps after be applied/modified/changed only once (since both enivornments are structured to be exactly the same).
so the following to me seems cleaner/easier to manage all steps regardless of environment:
let
Source = if Text.StartsWith(Parameter1, "http") then SharePoint.Files(Parameter1, [ApiVersion=15]) else Folder.Files(Parameter1)
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity", type text}, {"Customer", type text}, {"Phase", type text}, {"Owner", type text}, {"Division", type text}, {"Value", Currency.Type}, {"CloseDate", type date}, {"Status", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Customer"}),
#"Final" = #"Changed Type"
in
#"Final"- As it turns out, unfortunately, it looks like using an "if" statement to dictate the source within a query makes you lose the ability to refresh the dataset once published to the cloud.
Though everything works perfectly on the desktop, once the dataset is published, the service doesn't recognize the data source as being valid within the query, and therefore doesn't give you any options for refresh or editing credentials.
I tried writing the query your proposed way with the "if" statement outside of the source line as well as my method where the source line includes the if statement within it and both methods fail in the cloud.
So I think in reality what I want seems to be impossible.