Forum Discussion
64 bit Access database engine error when I have Power BI 32 bit
I'm a little confused - in that when I go to: https://www.eia.gov/petroleum/gasdiesel/xls/psw18vwall.xls
it immediately downloads the file and doesn't present a web page at all
so is this topic concerning a local file xls? as I don't see any connectibility by Power BI Service to this as a web page.....
Hi CahabaData,
I fetch the data as I would fetch it from a web source:
let
Source = Excel.Workbook(Web.Contents("https://www.eia.gov/petroleum/gasdiesel/xls/psw18vwall.xls"), null, true),
#"Data 1" = Source{[Name="Data 2"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Data 1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
#"Promoted Headers2" = Table.PromoteHeaders(#"Promoted Headers1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers2",{{"U.S. No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"East Coast No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"New England (PADD 1A) No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"Central Atlantic (PADD 1B) No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"Lower Atlantic (PADD 1C) No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"Midwest No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"Gulf Coast No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"Rocky Mountain No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"West Coast No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"California No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"West Coast (PADD 5) Except California No 2 Diesel Retail Prices (Dollars per Gallon)", type number}, {"Column13", type number}, {"Date", type date}})
in
#"Changed Type1"
This is not a local file and it is updated by a third party in a frequency I am not aware of (monthly). I want to always have the latest data available in my dashboard without the need to download the file as a local excel file.
If there is an alternative method of doing this, I am happy to learn.
Helena