Results 1 to 4 of 4

Thread: Big error in Power Query during web import.

  1. #1
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365

    Big error in Power Query during web import.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    For example, when I look at the website https://www.oddsportal.com/matches/hockey/20210907/, the sorting by time shows me the leagues (separate row) and below that the games of this league at this time.

    If I now import "Table 0" of this website using Power Query, then the first line with the text: "World "Club Friendly" is interpreted as a header line and all following lines with identical content are automatically deleted. So all friendlies starting at later times are now assigned to the league above.

    For this case, there should be the import option "Table without headers", where all rows are imported without Power Query automatically deleting rows.

  2. #2
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    123
    Articles
    0
    Excel Version
    Office 365
    When I link to that website, this is the M-Code generated
    Code:
    let
        Source = Web.Page(Web.Contents("https://www.oddsportal.com/matches/hockey/20210907/")),
        Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"World»Club Friendly", type text}, {"World»Club Friendly2", type text}, {"World»Club Friendly3", type text}, {"1", Int64.Type}, {"X", type text}, {"2", Int64.Type}, {"B's", type text}})
    in
        #"Changed Type"
    There is no "Promote First Row as Headers" step as the Table being imported already has the columns labeled. Power Query isn't deleting anything.
    If you delete the Changed Type and Navigation steps and click on the whitespace next to the Table value in row 1 you'll see the column names.
    If you don't like those headers, I would use the step Use Headers as First Row - this will demote the headers and rename the columns 1 - n.
    Then name the columns as needed.
    From there start your transforms.
    Last edited by Nick Burns; 2021-09-12 at 07:01 AM.
    Oh... by the way, YOU'RE WELCOME!

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    Hello Nick,

    thank you very much for your efforts, but I think you didn't understand the problem properly.

    Since the data is imported from the website sorted by time, it is a coincidence which league for a sport and a day is displayed first. And exactly the first line is interpreted by Power Query as header. And all following lines, whose content is identical with the automatically interpreted header line, are automatically deleted by Power Query, before a user has the chance to prevent this.

    If you look at the website, you will see the match Liptovsky Mikulas (Svk) - Znojmo (Cze) starting at 15:00 / 3:00 p.m.
    under World >> Club Friednly. But after import by Power Query this match is shown under Russia >> VHL.

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    In the navigator preview, the web view shows the "World >> Club Friendly" on for the match at 15:00 (3:00 p.m.). But already in the preview of the table view, it is displayed incorrectly.

    So for me it is clearly an error of Power Query.

    Click image for larger version. 

Name:	import oddsportal.- Navigator - web view.jpg 
Views:	10 
Size:	94.2 KB 
ID:	10633Click image for larger version. 

Name:	import oddsportal.- Navigator - table view.jpg 
Views:	10 
Size:	92.2 KB 
ID:	10634

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •