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 DaaSour...
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.
AlB
Community Champion
Community ChampionHi 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. |