Importing actualizing data from the web and saving it in a table containing all the actalizations

[Anonymous]

Hi !

I'm trying to create a table with the US gasoline prices of every week.

I went on a website to find it : https://www.eia.gov/petroleum/gasdiesel/, it looks like this :

I took the table of the previous values (from May 2019 to this day) and put it into power BI :

Now, I'm trying to find a way to automatically add a row to this table whenever a new price is released.

I imported the table with the new prices in Power BI but I don't know how to add the row since every time a new price is released, it replaces the latest one and moves every price to the left :

If I just add a new row with the latest price in my table, I will end up with having only the latest price in my table and not the other ones, and whenever a new price will be released, it will replace the previous one.

Do you understand ?

Please, if you have any idea on how I might do what I want, help me !

If I was unclear, tell me what you didn't understand.

Thank you very much.

Marion

[amitchandak]

@Anonymous , not very clear to me, but check if this can help

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[camargos88]

Hi @Anonymous ,

How did you get the data ?

Check if this m code works:

let
Source = Excel.Workbook(Web.Contents("https://www.eia.gov/petroleum/gasdiesel/xls/pswrgvwall.xls"), null, true),
#"Data 2" = Source{[Name="Data 1"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Data 2",{{"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}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Weekly U.S. Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly East Coast Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly New England (PADD 1A) Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly Central Atlantic (PADD 1B) Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly Lower Atlantic (PADD 1C) Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly Midwest Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly Gulf Coast Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly Rocky Mountain Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly West Coast Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type number}, {"Weekly Colorado Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Florida Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly New York Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Minnesota Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Ohio Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Texas Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Washington Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Cleveland, OH Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Denver, CO Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Miami, FL Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Weekly Seattle, WA Regular Conventional Retail Gasoline Prices (Dollars per Gallon)", type text}, {"Column22", type text}})
in
#"Changed Type1"

It gets the file link and once it's updated, you get the new values...using web.connector.