Power Query Definition based on column position rather than name RRS feed

  • Question

  • I am using the 'From Web' data source in power query to get data on an html table.  Everything works fine on the initial set up but I get an error when I refresh the data and the column name has changed.  I did notice that If I remove the change type steps where the column name is listed, the query will work and refresh data.  How can I still perform ChangedType or other functions by referencing column positions rather than column names? so if October 2012 was the second column in data source could I reference as (Data0,}{[2],type number}})

    Friday, August 23, 2013 3:40 AM


  • Hi,

    You can reference columns by position by leveraging the Table.ColumnNames() function, which returns a list of columns. Lists support indexed access.

    In your example above, you could do this by modifying the formula to the following:

    = Table.TransformColumnTypes(Data0,{{Table.ColumnNames(Data0){1}, type number}})

    Hope this helps. Thanks for using Power Query.


    Saturday, August 24, 2013 11:28 PM