excelpowerquerydynamic-columns

Excel: Power Query Editor: Data Source from Web has Changing Columns

I have a spreadsheet that has data for games played in a video game, sorted by hero, winrates, etc. In excel, using data source from web, and using the website here: https://www.heroesprofile.com/Global/Hero/?timeframe_type=minor&timeframe=2.50.1.79515&game_type=sl&map=Alterac+Pass populates as it should under table 0.

The problem is once new data is entered to the website, the table column headers change; then the refresh-able data source on excel gives errors such as this: Expression.Error: The column '48.59 Win Rate %' of the table wasn't found. (The winrate changed when new logs were uploaded; so I think the column header is the issue since it can't find it anymore.)

Is there a way in advanced editor to set it so the column headers just refresh along with all the rest of the data? Or a work around of some kind?

let
    Source = Web.Page(Web.Contents("https://www.heroesprofile.com/Global/Hero/?timeframe_type=minor&timeframe=2.50.1.79515&game_type=sl&map=Hanamura+Temple")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"AVG Hero", type text}, {"48.59 Win Rate %", type number}, {"18.01 Popularity %", type number}, {"11.36 Pick Rate %", type number}, {"6.65 Ban Rate %", type number}, {"66 | -29 Influence", Int64.Type}, {"989 Games Played", Int64.Type}, {"Avg Win Rate", type text}, {"", type text}})
in
    #"Changed Type"

The number of columns and rows rarely ever change if there's a way to set those generically.

Thank you in advance!

Solution

  • If the type of contents of the columns does not change, you can load the data and remove any processing steps that Power Query adds.

    If Power Query recognises the first row of the data as column headers, you can do the following:

    enter image description here

    Now you are in control of the column names and can have the same column name consistently, even if the column name changes in the source.

    Screenshot of the result in PQ enter image description here

    Code copied from the Advanced Editor

    let
    Source = Web.Page(Web.Contents("https://www.heroesprofile.com/Global/Hero/?timeframe_type=minor&timeframe=2.50.1.79515&game_type=sl&map=Hanamura+Temple")),
    Data0 = Source{0}[Data],
    #"Demoted Headers" = Table.DemoteHeaders(Data0),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "AVG Hero"}, {"Column2", "Win Rate"}, {"Column3", "Popularity"}, {"Column4", "Pick Rate"}, {"Column5", "Ban rate"}, {"Column6", "Influence"}, {"Column7", "Games Played"}, {"Column8", "Avg win rate"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1)
in
    #"Removed Top Rows"