locked
Load Data in Excel table does not work? RRS feed

  • Question

  • Hi,

    I have a problem and hope to find someone who could help me.

    I made a web query which goes through a list of url's and combines the results in one query.

    Unfortunately, it seems the new version of power query messed up something because it worked before I did the update.

    The problem is that if have loaded multiple url's and now want to expand the data (just like if you join 2 tables).

    Example Column:

    ABC123 "_" (Column Title)

    Error

    Error

    Error

    Table

    Table

    Table

    As you can see, there should be at least 3 datasets in the excel sheet if I expand the column "_".

    Funny thing is, in the query editor I can see the correct expanded values. But if I try to load them in Excel, excel will have 0 datasets.

    Since it worked in the previous version but not know I guessed it might be an error occurring because of the new version.

    Best wishes,

    Andreas

    Tuesday, May 23, 2017 8:58 AM

Answers

  • Hi Andreas,

    this script returns the source-data on my machine:

    let
        Source = Web.Page(Web.Contents("https://de.finance.yahoo.com/quote/MSFT/analysts?p=MSFT")),
        #"Added Index" = Table.AddIndexColumn(Source, "TableNumber", 1, 1),
        Filter = Table.SelectRows(#"Added Index", each ([ClassName] <> null)),
        DemHeader = Table.AddColumn(Filter, "DemHeader", each Table.DemoteHeaders([Data])),
        DynamicColNames = List.Union(List.Transform(DemHeader[DemHeader], each Table.ColumnNames(_))),
        #"Expanded Data" = Table.ExpandTableColumn(DemHeader, "DemHeader", DynamicColNames),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Caption", "Source", "Id", "Data"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by A.Hofmann Monday, August 21, 2017 12:05 PM
    Sunday, August 20, 2017 6:50 AM

All replies

  • Hi Andreas. So you're saying it works in the PQ Queries Editor window, but not when you load? That sounds like it could be a bug.

    Are you able to share your queries with us?

    Ehren

    Wednesday, May 24, 2017 5:30 PM
  • Hi Ehren,

    first of all thank you for the answer and sorry for my late reply.

    It seems that the main issue is that the Website itself changed something.

    If I try to make a single web query I get the error 301 in a Webbrowser.

    E.g. try to access: https://de.finance.yahoo.com/quote/MSFT/analysts?p=MSFT

    first via Webbrowser, secondly via power query.

    I am not sure how Power Query works, maybe I got the result of the last functioning query shown in the query editor.

    I have to look a bit longer into it.

    Best wishes,

    Andreas


    • Edited by A.Hofmann Wednesday, June 7, 2017 7:38 AM new info
    Wednesday, June 7, 2017 7:32 AM
  • Hi Andreas,

    Did you make any progress with this?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, August 17, 2017 6:26 PM
  • Hi Michael,

    no unfortunatly I could't find a solution with power query.It seems that yahoo has done something which prevents the Access via power query.

    Since I was unable to find a solution I switched to a different site.

    But please let me know if you find a solution!

    Best wishes,

    Andreas

    Friday, August 18, 2017 7:39 AM
  • Hi A.Hofmnn,

    If you're able to share the query, another member of the forum or myself can take a closer look.

    Is this something you can do?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, August 18, 2017 4:00 PM
  • Hi Andreas,

    this script returns the source-data on my machine:

    let
        Source = Web.Page(Web.Contents("https://de.finance.yahoo.com/quote/MSFT/analysts?p=MSFT")),
        #"Added Index" = Table.AddIndexColumn(Source, "TableNumber", 1, 1),
        Filter = Table.SelectRows(#"Added Index", each ([ClassName] <> null)),
        DemHeader = Table.AddColumn(Filter, "DemHeader", each Table.DemoteHeaders([Data])),
        DynamicColNames = List.Union(List.Transform(DemHeader[DemHeader], each Table.ColumnNames(_))),
        #"Expanded Data" = Table.ExpandTableColumn(DemHeader, "DemHeader", DynamicColNames),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Caption", "Source", "Id", "Data"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by A.Hofmann Monday, August 21, 2017 12:05 PM
    Sunday, August 20, 2017 6:50 AM
  • Hi Imke,

    first of all thank you very much for your solution.

    Actually it seems to work and I was surprised how simple the solution was.

    Because I remembered that my code was very similar I tried to find out what created the problem in my original code.

    But very surprisingly my old query now also seems to work again (in most parts, because the site structure has changed)!

    I am not really sure what fixed the issue.

    I guess it could be one of two things.

    1. I have updated in the meantime from Excel 2010 (power query plugin) to Excel 2016 where power query is now a Standard Feature.

    2. The site seem to have underwent some changes, maybe they have changed something again and now it works again (I discovered the issue nearly 6 month ago).

    Anyway thank you all very much for your help!

    Best wishes,

    Andreas


    • Edited by A.Hofmann Monday, August 21, 2017 12:06 PM mistake
    Monday, August 21, 2017 12:05 PM