Creating a Historical Table from a Blank Query

[basimretal]

Hello all,

I managed to connect to a data source using the below code. However, the challenge is that the output is one row, and with every refresh, it updates the same row. I want to create a historical table so I can visualize the the stock price on a line chart since I already have LastUpdateTime column to determine the data/time.

"let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://webservices.tadawul.com.sa/Tadawul_WebAPI/services/GetDetailQuote", [Headers=[#"Content-Type"="text/xml; charset=utf-8", SOAPAction=""], Content=Text.ToBinary(
"<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:ser='http://services.RSS.tadawul.com'>
<soapenv:Header/>
<soapenv:Body>
<ser:getDetailQuoteForCompany>
<companyId>####</companyId>
<secureKey>####</secureKey>
</ser:getDetailQuoteForCompany>
</soapenv:Body>
</soapenv:Envelope>"
)]), null, null, 65001)}),
#"Parsed XML" = Table.TransformColumns(Source,{},Xml.Tables),
Column1 = #"Parsed XML"{0}[Column1],
Table = Column1{1}[Table],
Table1 = Table{0}[Table],
Table2 = Table1{0}[Table],
Table3 = Table2{0}[Table],
Table4 = Table3{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table4,{{"id", Int64.Type}, {"companyName", type text}, {"companyNameAr", type text}, {"companyLongName", type text}, {"companyLongNameAr", type text}, {"lastTradePrice", type number}, {"lastTradeQuantity", Int64.Type}, {"lastUpdateTime", type datetime}, {"open", type number}, {"highPrice", type number}, {"lowPrice", type number}, {"prevClosePrice", type number}, {"askPrice", type number}, {"askQuantity", Int64.Type}, {"bidPrice", type number}, {"bidQuantity", Int64.Type}, {"noOfTrades", Int64.Type}, {"turnOver", type number}, {"volumeTraded", Int64.Type}, {"avgTradeSize", Int64.Type}, {"changeAmount", type number}, {"changePercentage", type number}, {"change52week", type number}, {"high52weekDate", type date}, {"high52WeeksPrice", type number}, {"low52weekDate", type date}, {"low52WeeksPrice", type number}, {"startOfYearPrice", type number}, {"yearAgoPrice", type number}, {"bookValue", type number}, {"closePrice", type number}, {"earning", type number}, {"EPS", type number}, {"marketCap", Int64.Type}, {"numberOfShares", Int64.Type}, {"numberOfSharesFloated", type number}, {"PBValue", type number}, {"PERatio", type number}})
in
#"Changed Type"
"

This is the output:

[Anonymous · Accepted solution]

Hi @basimretal ,

According to your question, you want to get the value of each time, as a history for overlay right, but due to the limitation of power query, he will only get the latest value every time he executes the M language code, there is no way to do an effective record of the history value, my suggestion is that you can use python on the newly created csv file as a history of the value of the archives, and then use the power bi.

Below are the related cases I found for you which have been solved, I hope it will help you to solve your problem!
Solved: PowerBI API Integration - Microsoft Fabric Community

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[basimretal]

Do you think there is a possible solution using Power Automate?

[Anonymous]

Hi @basimretal ,

I'm glad that my answer is recognized by you, I think it's possible if you use Power Automate, here are some of my thought process:
1. Trigger: trigger at regular intervals (e.g. every hour).
2. HTTP request: send HTTP GET request to CoinGecko API to get data.
3. Parse JSON: extract the data returned by the API (e.g. current price).
4. Append to CSV: Append data to data.csv file stored in OneDrive or SharePoint.
This is just part of my thinking, if you need a more perfect program, you can go to the Power Automate forum for more detailed help, I hope these ideas can help you, for you to open your mind!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.