cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lev_rc
New Member

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

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @lev_rc 

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"

 

SU18_powerbi_badge

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.

 

View solution in original post

3 REPLIES 3
lev_rc
New Member

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
Super User
Super User

Hi @lev_rc ,

 

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
Super User
Super User

Hi @lev_rc 

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"

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors