Search code examples
excelpowerquery

Merging tables with condition on powerquery - Wait for Table in web

I asked this question a while ago and got an answer that suited me:

Merging tables with condition on powerquery

However, I have a problem, my current code combines the year tables correctly, but at the beginning of the year on the first day the code will try to merge with the 2024 table for example, but it will not be available in the first days of the year, so I would like that the code would only use the table if it is available, for example we are on 01/01/2025 so the code will wait for the table from the year 2025, here is my current code:

let
currentYear = Date.Year(DateTime.LocalNow()),
tableList = List.Transform({2011..currentYear}, each "INCC_" & Text.From(_)),
combinedTable=Table.Combine(List.Transform(tableList, each Record.Field(Record.SelectFields(#shared,{_}),_)))
in
combinedTable

Tables that the code combines:

enter image description here

Code from one of the tables, note that Data and Fonte adds +1 as the year progresses:

let
    Fonte = Web.Page(Web.Contents("http://indiceseconomicos.secovi.com.br/indicadormensal.php?idindicador=59")),
    Data29 = Fonte{29}[Data],
    #"Tipo Alterado" = Table.TransformColumnTypes(Data29,{{"Header", type text}, {"Mês", type text}, {"índice", type number}, {"Var% Mês", Percentage.Type}, {"Acum. Ano%", Percentage.Type}, {"Acum. 12 meses%", Percentage.Type}})
in
    #"Tipo Alterado"
Solution

  • Added a new step tableListWithRows. See if that helps.

    let
currentYear = Date.Year(DateTime.LocalNow()),
tableList = List.Transform({2011..currentYear}, each "INCC_" & Text.From(_)),
tableListWithRows = List.Select(tableList, each try Table.RowCount(Record.Field(#shared,_)) > 0 otherwise false),
combinedTable = Table.Combine(List.Transform(tableListWithRows, each Record.Field(Record.SelectFields(#shared,{_}),_)))
in
combinedTable