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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
basimretal
Frequent Visitor

Creating a Historical Table from a Blank Query

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:

screenshot.png

1 ACCEPTED SOLUTION
v-xingshen-msft
Community Support
Community Support

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.

vxingshenmsft_0-1737006966199.pngBelow 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.

 

View solution in original post

3 REPLIES 3
v-xingshen-msft
Community Support
Community Support

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.

vxingshenmsft_0-1737006966199.pngBelow 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.

 

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

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.



Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.