Hey,
I need to build a table with xml files from an API.
(data as text) =>
let
SourceURL = "https://ws-mercado.ren.pt/MarketInfoService.asmx", //host provides this address. Url ends often with "wsdl"
options = [ #"Accept-Encoding"= "gzip,deflate",
#"SOAPAction"="https://ws-mercado.ren.pt/GetInfoForTimeFrameByInfoType",
#"Content-Type"="text/xml;charset=UTF-8",
#"Connection"="Keep-Alive"
],
Source = Xml.Tables(Web.Contents(SourceURL,
[Content=Text.ToBinary("
<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:ws=#(0022)https://ws-mercado.ren.pt#(0022)>
<soapenv:Header/>
<soapenv:Body>
<ws:GetInfoForTimeFrameByInfoType>
<!--Optional:-->
<ws:EndDay>"&data&"</ws:EndDay>
<ws:StartDay>"&data&"</ws:StartDay>
<!--Optional:-->
<ws:InfoType>"&process&"</ws:InfoType>
</ws:GetInfoForTimeFrameByInfoType>
</soapenv:Body>
</soapenv:Envelope>"),
Headers=options])),
#"Table Expandida" = Table.ExpandTableColumn(Source, "Table", {"Name", "Table"}, {"Name.1", "Table.1"}),
#"Table.1 Expandida" = Table.ExpandTableColumn(#"Table Expandida", "Table.1", {"Name", "Table"}, {"Name.2", "Table"}),
#"Table Expandida1" = Table.ExpandTableColumn(#"Table.1 Expandida", "Table", {"GetInfoForTimeFrameByInfoTypeResult"}, {"GetInfoForTimeFrameByInfoTypeResult"}),
GetInfoForTimeFrameByInfoTypeResult = #"Table Expandida1"{0}[GetInfoForTimeFrameByInfoTypeResult],
#"XML Analisado" = Xml.Tables(GetInfoForTimeFrameByInfoTypeResult),
#"Table Expandida2" = Table.ExpandTableColumn(#"XML Analisado", "Table", {"Name", "Table"}, {"Name.1", "Table.1"}),
#"Table.1 Expandida1" = Table.ExpandTableColumn(#"Table Expandida2", "Table.1", {"MARKETDAY", "PERIOD", "UTCDATE", "SETTLEUNIT", "IMBALANCE"}, {"MARKETDAY", "PERIOD", "UTCDATE", "SETTLEUNIT", "IMBALANCE"})
in
#"Table.1 Expandida1"
let
Origem = List.Dates (#date(2022,1,1), 365 * 1, #duration(1,0,0,0)),
#"Convertido em Tabela" = Table.FromList(Origem, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Convertido em Tabela",{{"Column1", type date}}),
#"Linhas Filtradas" = Table.SelectRows(#"Tipo Alterado", each [Column1] <= Date.From(DateTime.LocalNow())),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Linhas Filtradas",{{"Column1", type text}}),
#"Personalizado Adicionado" = Table.AddColumn(#"Tipo Alterado1", "Personalizado", each Text.End([Column1],4) & "-" & Text.Start(Text.End([Column1],7),2) & "-" & Text.Start([Column1],2)),
#"Colunas com Nome Mudado" = Table.RenameColumns(#"Personalizado Adicionado",{{"Personalizado", "Data"}}),
#"Colunas Removidas" = Table.RemoveColumns(#"Colunas com Nome Mudado",{"Column1"}),
#"Added Custom" = Table.AddColumn(#"Colunas Removidas", "XML Data", each getXML_web([Data])),
#"XML Data Expandida" = Table.ExpandTableColumn(#"Added Custom", "XML Data", {"Name", "Name.1", "MARKETDAY", "PERIOD", "UTCDATE", "SETTLEUNIT", "IMBALANCE"}, {"Name", "Name.1", "MARKETDAY", "PERIOD", "UTCDATE", "SETTLEUNIT", "IMBALANCE"}),
#"Colunas Removidas1" = Table.RemoveColumns(#"XML Data Expandida",{"Data", "Name", "Name.1", "UTCDATE"}),
#"Tipo Alterado2" = Table.TransformColumnTypes(#"Colunas Removidas1",{{"MARKETDAY", type date}, {"PERIOD", Int64.Type}, {"SETTLEUNIT", type text}}),
#"Linhas Filtradas1" = Table.SelectRows(#"Tipo Alterado2", each ([SETTLEUNIT] = "ALARES")),
#"Valor Substituído" = Table.ReplaceValue(#"Linhas Filtradas1",".",",",Replacer.ReplaceText,{"IMBALANCE"}),
#"Tipo Alterado3" = Table.TransformColumnTypes(#"Valor Substituído",{{"IMBALANCE", type number}})
in
#"Tipo Alterado3"
So far, I've made it.
In order to optimize the code, I wanted to run the function and complete the table only on dates that do not yet have values.
What this best method? IF? Or ohter method?
Thank you.
