Loop with Power Query

[Bruzio]

I'm discovering the power of Power Query, I'd like to convert this loop from xmlhttp

This code is an example

    With Worksheets("alfa")
    
    For r = 5 To 100

        id = .Cells(r, "A").Value
        If id <> "" Then

        With httpReq
            .Open "GET", "http://www.nowgoal.pro/football/match/h2h-" & id, False
            .setRequestHeader "Content-Type", "application/json"
            .Send
            
            While httpReq.readyState <> 4
                DoEvents
            Wend
            
            Set json = JsonConverter.ParseJson(.ResponseText)
        End With
          
        End If

    Next r
    End With

I've prepared two queries, so two different sheets.

In the sheet alfa I have the list of IDs that I download with

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("http://www.nowgoal.pro/gf/data/bf_us.js"), null, null, 1252)}),
    #"Removed Top Rows" = Table.Skip(Source,5),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29", "Column1.30", "Column1.31", "Column1.32", "Column1.33", "Column1.34", "Column1.35"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}, {"Column1.3", Int64.Type}, {"Column1.4", Int64.Type}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", Int64.Type}, {"Column1.10", Int64.Type}, {"Column1.11", Int64.Type}, {"Column1.12", Int64.Type}, {"Column1.13", Int64.Type}, {"Column1.14", Int64.Type}, {"Column1.15", Int64.Type}, {"Column1.16", Int64.Type}, {"Column1.17", Int64.Type}, {"Column1.18", type text}, {"Column1.19", type text}, {"Column1.20", Int64.Type}, {"Column1.21", type text}, {"Column1.22", Int64.Type}, {"Column1.23", type text}, {"Column1.24", Int64.Type}, {"Column1.25", type text}, {"Column1.26", Int64.Type}, {"Column1.27", type text}, {"Column1.28", Int64.Type}, {"Column1.29", Int64.Type}, {"Column1.30", Int64.Type}, {"Column1.31", Int64.Type}, {"Column1.32", type text}, {"Column1.33", type text}, {"Column1.34", type text}, {"Column1.35", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.1", Splitter.SplitTextByEachDelimiter({"["}, QuoteStyle.Csv, true), {"Column1.1.1", "Column1.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type text}, {"Column1.1.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.1.1"})
in
    #"Removed Columns"

ID	Column1.2	Column1.3	Column1.4	Column1.5	Column1.6
1996528​	35​	16128​	26684​	'Persela Lamongan'	'Madura United'
1985075​	21​	9916​	4025​	'FK Andijon'	'Mashal Muborak'
1991696​	19​	5890​	5903​	'Floreat Athena'	'Perth SC'
1991698​	19​	5892​	5897​	'Inglewood United'	'ECU Joondalup'
1997475​	35​	1986​	2225​	'KuPs'	'KPV'

In the sheet beta I should download the odds for each ID in column ID

let
    Source = Web.Page(Web.Contents("http://www.nowgoal.pro/football/match/h2h-"&id)),
    Data0 = Source{0}[Data]
in
    Data0

Type	Early	Early2	Early3
HDP	1.05 0/0.5 0.75	1.05 0/0.5 0.75	1.05 0/0.5 0.75
O/U	0.90 2 0.90	0.90 2 0.90	0.90 2 0.90

The goal is to have the IDs and odds in the same sheet

1996528​				1.05 0/0.5 0.75	1.05 0/0.5 0.75	1.05 0/0.5 0.75	0.94 0/0.5 0.86
1985075​
1991696​
1991698​
1997475​

Can you give me an idea to think about?

Thanks

 

[Bruzio]

Hi, any ideas?