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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors