none
Ranking within power query RRS feed

  • Question

  • I have a dataset that looks like this:

    Primary Industry Date AveValue
    Aluminum 19/10/2007 0.3233%
    Aluminum 12/10/2007 0.1967%
    Aluminum 05/10/2007 0.3367%
    Aluminum 28/09/2007 0.2333%
    Aluminum 21/09/2007 0.0100%
    Aluminum 14/09/2007 -0.1933%
    Aluminum 07/09/2007 -0.0333%
    Airlines 13/03/2015 1.0367%
    Airlines 06/03/2015 0.7200%
    Airlines 27/02/2015 0.6422%
    Airlines 20/02/2015 0.9100%
    Airlines 13/02/2015 0.7667%
    Airlines 06/02/2015 1.0178%

    I am trying to calculate a rank for the primary industries for each date based up the AveValue.

    So far my M query looks like this:

    let
        Source = IndustryRank,
        GroupedRows = Table.Group(Source, "Date", {"Data", each _, type table}),
        Rank = Table.TransformColumns( GroupedRows, { "Data", each Table.AddIndexColumn( Table.Sort( _, {"AveValue", Order.Descending } ), "Rank",1,1) } ),
        Expand = Table.ExpandTableColumn(Rank, "Data", {"Primary Industry", "Rank"}, {"Primary Industry", "Rank"})
    in
        Expand

    However I am getting the following error message:

    Expression.Error: Cannot apply operator - to types Text and Number.
    Details:
        Operator=-
        Left=Data request failed: Incorrect symbol
        Right=-0.0005

    I believe my code is almost there - can anyone help me resolve the issue?

    Howard


    Wednesday, March 18, 2015 11:27 AM

Answers

  • Thanks for the response.  I found my problem - I had kept errors in a preceding step so even though the AveValue was formatted correctly the errors were throwing me off.....

    • Marked as answer by n_1ghtwatchman Wednesday, March 18, 2015 4:28 PM
    Wednesday, March 18, 2015 4:27 PM

All replies

  • Hi Howard,

    I agree - your code looks as if it should be able to do the job .

    Is the column "AveValue" explicitely formatted as decimal number?


    Imke


    Wednesday, March 18, 2015 12:50 PM
    Moderator
  • Thanks for the response.  I found my problem - I had kept errors in a preceding step so even though the AveValue was formatted correctly the errors were throwing me off.....

    • Marked as answer by n_1ghtwatchman Wednesday, March 18, 2015 4:28 PM
    Wednesday, March 18, 2015 4:27 PM