Forum Discussion
Possible to use IF statement in data sources, to switch between Web.Contents() or File.Contents("")
Hello All,
Can I use conditional IF statement to select the datasource in Excel PowerQuery?
In a normal/default situation, when selecting csv or json as a data source when adding an Excel DaaSource the path is absolute, which is not helpfull when sharing it with colleagues. Not everybody saves the files in the same directory and some save it in OneDrive/Sharepoint via Office365. So my goal is to make this work for everyone everywhere.
Below (snippet 1) you can see a csv DataSource which connects to a common sharepoint site, therefor the use of the function Web.Contents. I am using a dynamic path in Excel via a formule which refers to the variable 'Excel Path' (see snippet 2). Since Windows10/Office365 automaticly subsititues the local file path (C:\Users\.. e.g.) with the sharepoint http url when the file is saved within a OneDrive location, this forces me to move this outside the OneDrive, make it portable with Excel Formules, PowerQuery M or VB scripting.
Technically I need an conditional IF statement to switch between the two. Is this possible? What other possablities are there to get the same result?
Csv.Document(Web.Contents(FullPathToFile)
vs.
Csv.Document(File.Contents(FullPathToFile)
PowerQuery - DataSource Snippet #1
let
WBPath = Excel.CurrentWorkbook(){[Name="ExcelPathEnv"]}[Content]{0}[Excel Path],
FullPathToFile = WBPath & "CsvImports/AWS/interfaces.csv",
Source = Csv.Document(Web.Contents(FullPathToFile), [Delimiter=";", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"server_name", type text}, {"account_name", type text}, {"primary_ip", type text}, {"disaster_recovery_ip", type text}, {"interface", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"interface"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"server_name", Order.Ascending}})
in
#"Sorted Rows"
Excel Snippet #2 (used to fetch the current location of the file
=LEFT(CELL("filename";J3);FIND("[";CELL("filename";J3);1)-1)
Thanks in advance!
Regards Rick
Hi Anonymous
How exactly would you differentiate between the two cases? If I understand correctly, one case would have the http in the path and the other wouldn't. If so, you can use that for the if statement:
let WBPath = Excel.CurrentWorkbook(){[Name="ExcelPathEnv"]}[Content]{0}[Excel Path], FullPathToFile = WBPath & "CsvImports/AWS/interfaces.csv", Source = if Text.Contains(Text.Upper(WBPath),"HTTP") then Csv.Document(Web.Contents(FullPathToFile), [Delimiter=";", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]) else Csv.Document(File.Contents(FullPathToFile), [Delimiter=";", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"server_name", type text}, {"account_name", type text}, {"primary_ip", type text}, {"disaster_recovery_ip", type text}, {"interface", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"interface"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"server_name", Order.Ascending}}) in #"Sorted Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
3 Replies
Hi Anonymous ,
Yes you can use IF for source.
In your case, you can apply try otherwise
Let
SourceWeb = Csv.Document(Web.Contents(FullPathToFile),
SourceFile = Csv.Document(File.Contents(FullPathToFile),Source = try SourceWeb otherwise SourceFile
in
Source
The above code will try the first source and if error then if will try the second source.
Regards
KT
Hi Anonymous
How exactly would you differentiate between the two cases? If I understand correctly, one case would have the http in the path and the other wouldn't. If so, you can use that for the if statement:
let WBPath = Excel.CurrentWorkbook(){[Name="ExcelPathEnv"]}[Content]{0}[Excel Path], FullPathToFile = WBPath & "CsvImports/AWS/interfaces.csv", Source = if Text.Contains(Text.Upper(WBPath),"HTTP") then Csv.Document(Web.Contents(FullPathToFile), [Delimiter=";", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]) else Csv.Document(File.Contents(FullPathToFile), [Delimiter=";", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"server_name", type text}, {"account_name", type text}, {"primary_ip", type text}, {"disaster_recovery_ip", type text}, {"interface", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"interface"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"server_name", Order.Ascending}}) in #"Sorted Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
