none
Two Excel Web Query (IQY) features I am missing when using Power Query RRS feed

  • Question

  • 1. header labels

    In Excel Web Queries, I could specify the heading label

    2. format expressions

    In Excel Web Queries, I could specify a format string.

    I'm not too concerned about #1, but #2 is a real issue. Perhaps is just a lack of feature understanding on my part, but I don't see in the Query Editor any way to change the format for a column.  I can change the format in the worksheet but that change is lost when I refresh the data. Can formatting be done via "M"?  If so, then a feature request would be to add formatting to UI.

    Reading the docs, I see Number.ToText()

    http://office.microsoft.com/en-us/excel-help/number-totext-HA104110114.aspx

    But I don't get how I can use this in a Table.TransformColumns
    Friday, January 10, 2014 1:50 AM

Answers

  • This is something we'd like to implement eventually, but I'm not able to suggest a specific timeframe. In general, our goal is to support the majority of things that you can do with the Data tab in native Excel, and this feature falls into that category.
    Friday, January 10, 2014 5:59 PM

All replies

  • The editor supports both renaming columns and changing their type. The details depend on the exact version you're using, but in the latest version of Power Query these functions are accessible both by right-clicking on the column and making the appropriate menu choice and -- in the case of "change type" -- by selecting the column and using the "Data Type" dropdown in the ribbon.
    Friday, January 10, 2014 4:14 PM
  • Hi Curt,

    I am familiar with changing type. What I need is to set the number format. 

    Friday, January 10, 2014 4:22 PM
  • Ah, I did not understand "format" as "number format". As you've noticed, there's no notion of number format in Power Query itself, so you can only format the number by transforming it into another value. Number.ToText has a variety of options for transforming a number into a string. The following will format the "Column" column to have four places after the decimal point.

    = Table.TransformColumns(Data,{{"Column", each Number.ToText(_, "f4")}})

    Be aware, though, that this will cause the cell value in Excel to have a type of Text instead of Number.

    Friday, January 10, 2014 4:53 PM
  • Is that an underscore characters as the first argument?
    Friday, January 10, 2014 5:03 PM
  • Just tested with underscore and it works.

    As you pointed out, this causes the cell value in Excel to have type of text. Is there a better approach now or coming down the pipe?

    Friday, January 10, 2014 5:10 PM
  • This is something we'd like to implement eventually, but I'm not able to suggest a specific timeframe. In general, our goal is to support the majority of things that you can do with the Data tab in native Excel, and this feature falls into that category.
    Friday, January 10, 2014 5:59 PM