Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a Power BI file in which I'd like to be able to switch the connection to the source data between local and cloud - the data is structured in exactly the same way in both environments - and I'm looking to parameterize the connection string so that I can switch between environments easily.
I'm have a parameter set to have the connection enviorment defined as
LocalData = "https://test.sharepoint.com/sites/testsite" OR LocalData = "C:\local path name" depending on the value chosed in my parameter.
When I select the sharepoint connection, I'd like the query to change the surrounding connection string to be 'SharePoint.Files (LocalData, [ApiVersion = 15])' and when I select the local source, I'd like the full connection string to be Folder.Files(LocalData).
I've tried to hard code the "SharePoint.Files" and "Folder.Files" portions of the connection command into the parameter, but in doing so - the whole parameter string, i.e., Folder.Files("C:\local path name") is only passed as text - so it doesn't know to use the Folder.Files part of the parameter as a connection command... is there any way to parameterize both the WHERE of my data (i.e. the source data location), as well as HOW the data should be connected (i.e. the source data connection method)?
let Source = SharePoint.Files(LocalData, [ApiVersion = 15]) in Source
let Source = Folder.Files(LocalData) in Source
Solved! Go to Solution.
Hmm, that's an interesting issue because you are changing the the data source type. I think the way that I would attack the problem would be to create a query for each source. Then, you could either combine those into a single query and use an "if" M statement to recognize the intended source (check the first couple characters to see if they are "http" for example and then select the correct import statements based upon this. You might want to convert your base queries to M functions for example and then your main query could have the if and just call the correct function to return the results.
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"
Nevermind - Got it! the below works perfectly.
let Source = if Text.StartsWith(Connection,"http") then SharePointSource else LocalSource in Source
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |