Possible to use IF statement in data sources, to switch between Web.Contents() or File.Contents("")

[Anonymous]

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

[Anonymous]

Hey all,

Sorry for the late reply, also needed to focus on other parts in the project. i've used the solution of Alb to make this sheet portable. Many thanks for the help. 

RC

[KT_Bsmart2gethe]

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

[AlB · Accepted solution]

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.