Forum Discussion

user_none's avatar
user_none
New Member
4 years ago

M-editor using excel as a reference for web

hi!

 

i have 50 similar web direction i need to scrap, the list is in a excel file

in the M editor it look like this

 

 

 

 

let
    Origen = Web.Page(Web.Contents("https://es.finance.yahoo.com/quote/ANTARCHILE.SN/history?period1=946944000&period2=1632268800&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true")),
    Data0 = Origen{0}[Data],
    #"Tipo cambiado" = Table.TransformColumnTypes(Data0,{{"Fecha", type text}, {"Abrir", type text}, {"Máx.", type text}, {"Mín.", type text}, {"Cierre*", type text}, {"Cierre ajus.**", type text}, {"Volumen", type text}})
in
    #"Tipo cambiado"

 

 

 

 So my question is, there is  a way to guide the m editor through the 50 similar web directions using the excel as a reference?

the first will be :

https://es.finance.yahoo.com/quote/ANTARCHILE.SN/history?period1=946944000&period2=1632268800&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true

and is the B1 in Excel

i want to put the second as B2 , the third as B3 and so on

Need Help

1 Reply

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi user_none,

     

    You may run into some challenges if they're only "similar" but you could try some variation of this...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PczdCoIwGMbxe/EC3EcyNBghEhSEB9WZeSDbay7WpvsIvPsKrdPf8/BvmmQIYfRbhHplOiMgnbvB2lTYJ5qiDYDK+lqeq8PxtE8vNRqUD9bNuxGcspLwImNFlmGMbxFjyhamnLANpSzPf65MAPfqNCdygV7pj/A1t5qDKYIR8/+ljNBRQikf0QeQlbYeeHARlvkehbDxm+Ykads3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"address", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web.Page(Web.Contents([address]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.First([Custom])),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Added Custom1", "Custom.1", {"Data"}, {"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Expanded Custom.1", "Data", {"Date", "Open", "High", "Low", "Close*", "Adj Close**", "Volume"}, {"Date", "Open", "High", "Low", "Close*", "Adj Close**", "Volume"})
in
    #"Expanded Data"

     

    Just replace the source with your Excel data if it works for you.

     

    Kim