Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 @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"

 

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
Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

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

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"

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors