Power query scrape web data from xhtml

[gargamalebarbosa]

Hi, When the date changes in the example link, the table changes but the URL address remains the same. In this case, how can I retrieve the new table when the date is modified?
I will take DATE information from sheet in any cell. I need a solution with PowerQuery

Link : EBİS Bildirim Sistemi

Thank you,

 

[alansidman]

If I am understanding you correctly, all you need to do is click on Refresh All and the table will update.

 

[gargamalebarbosa]

If I am understanding you correctly, all you need to do is click on Refresh All and the table will update.

Hi,
* İ will create a table with powerquery. And for example i have DATE value in F1 cell.
* When i change DATE in F1 cell and refresh my query, i want to table update with new DATE info.

 

[alansidman]

I suggest that you create a Parameter Query where the date is a variable in your Mcode. Can you provide your current Mcode and indicate where the date appears in your code that shows the URL

 

[gargamalebarbosa]

"I couldn't formulate the M code as I don't have any code , unfortunately. I'll keep trying for a while, maybe I'll make some progress.

 

[alansidman]

When I open your link to the Web page and change the date, there is no change in the URL. Without this as a variable in the URL, I cannot assist any further. I suspect that this will require someone with HTML coding experience. Good Luck.

 

[smozgur]

Try the following code. I commented the code, so it basically explains how it works.
Examine the steps as they will help to understand the method.

let
    // There is a named range in the workbook, reportDate
    // The date value is taken from the named cell
    // Make sure it is a valid date entered into the cell, so the following expression can convert it to the require format 
    reportDate = Date.ToText(Date.From(Excel.CurrentWorkbook(){[Name="reportDate"]}[Content]{0}[Column1]), [Format="dd.MM.yyyy"]),

    // The reportDate identifier is supposed to returna text
    // You can test it by enabling the following line if you experience problems with the named range
    // reportDate = "13.07.2023",

    // This is tricky, and requires more than HTML knowledge
    // It is a lot to explain here, but simply need to monitor the actual page request/response,
    // Find the necessary query elements and values from the request to form the actual URL
    Source = Web.Page(Web.Contents("https://bildirim.epdk.gov.tr/bildirim-portal/faces/pages/tarife/petrol/yonetim/bultenSorgula.xhtml?bultenKriterleriForm%3Aj_idt32=bultenKriterleriForm%3Aj_idt32&bultenKriterleriForm=bultenKriterleriForm&bultenKriterleriForm%3Aj_idt30_input=" & reportDate & "&javax.faces.ViewState=")),
    
    // This web service returns HTML code as a whole page
    // Beyond that, it returns unknown number of elements, 
    // Therefore, we need to find the table we need to get
    FindTable = Table.SelectRows(Source, each Table.ColumnNames([Data]){0} = "Yakıt Tipi"),

    // If the requested table is found, then expand it
    Result = if Table.RowCount(FindTable) = 1 then FindTable{0}[Data] else null
in
    Result

 

[gargamalebarbosa]

@smozgur thank you so much for your response. But i think I'm making some mistakes. When i change date info ,result does not change.. I will try to find what is wrong.
Teşekkür ederim ,sağolun.

 

[gargamalebarbosa]

I found my mistake and it is working perfect. Thank you. / Sağolun.

 

[smozgur]

I found my mistake and it is working perfect. Thank you. / Sağolun.

Glad to hear it helps. (Rica ederim.)