none
Powerquery loop through web tables with variable header names RRS feed

  • Question

  • Hieveryone,

    I'm building a personal spreadsheet, that analyzes the soccer betting results of a work competition. We are using the web service called www.kicktipp.de.

    I would now like to download the data for each of the matchadays from the following websites:

    http ://www. kicktipp.de/zalando2013/tippuebersicht?rankingGruppeId=0&tippspieltagIndex=16

    as you can see, the last number is the number of the matchday (1 to 34 over a whole season).

    I've written the following PowerQuery function that can get an individual table:

    let
        ScrapeResult = (i) => let
    	Source = Web.Page(Web.Contents("http://www.kicktipp.de/zalando2013/tippuebersicht?teilnehmerSucheName=&sortBy=gesamtpunkte&wertung=einzelwertung&rankingGruppeId=0&tippspieltagIndex=" & Text.From(i))),
        	Data2 = Source{2}[Data]
    	in 
    	Data2
    in
        ScrapeResult

    Ten I created a table with a column numbering 1 to 34 and added my function as a custom column.

    Now, the problem is that each individual table has varying header names. such as "FCB 2-1 BVB"

    so when I try to expand my "1 to 34"- table, I get a different column for each individual match.

    First question here:

    • is there a way to change the header names to something variable, so that i can call them Game 1, Game 2, etc?

    I have stored the game information somewhere else, so I know how what each matchday looks like and what the results were, too.

    having those 9 columns (9 games each match-day) would enable me to unpivot those columns, split the scores, format everything and have a nicely structured database. I.e. my desired final table would look like this:

    Matchday Game	Player	Bet Home Bet Away
    1	 1	Konrad	1	 2
    1	 2	Konrad	2	 0
    2	 1	Paul	3	 1
    …	 …	…	…	 …
    
    I'm looking forward to you help.

    Cheers Konrad.


    Monday, January 27, 2014 10:31 AM

Answers

  • Renaming by column position is a little tricky; I don't know a better way than getting the old column names and then building a set of new names. Here's something that worked for me:

    let
        ScrapeResult = (i) => let
            Source = Web.Page(Web.Contents("http://www.kicktipp.de/zalando2013/tippuebersicht?teilnehmerSucheName=&sortBy=gesamtpunkte&wertung=einzelwertung&rankingGruppeId=0&tippspieltagIndex=" & Text.From(i))),
            Data2 = Source{2}[Data],
            OldColumnNames = Table.ColumnNames(Data2),
            NewColumnNames =  {"Pos", "+/-", "Name", "Game1", "Game2", "Game3", "Game4", "Game5", "Game6", "Game7", "Game8", "Game9", "Pts", "Wins", "Tot"},
            Renamed = Table.RenameColumns(Data2, Table.ToColumns(Table.FromRows({OldColumnNames, NewColumnNames})))
        in
     Renamed,
        Source = ScrapeResult(2)
    in
        Source

    • Proposed as answer by Curt Hagenlocher Monday, January 27, 2014 4:44 PM
    • Marked as answer by kschaefers Tuesday, January 28, 2014 5:14 PM
    Monday, January 27, 2014 4:43 PM