none
Add Custom Column , OFFSET? RRS feed

  • Question

  • Hi There,

    I work for a broadcaster in New Zealand and am using Powerquery to analyse Ratings and performance over time. I have imported data  from our analysis system.

    The problem:

    We have one channel that plays at real-time and the same channel played one hour later (Plus One we call it). I have two columns for each channel with ratings (number format).

    I would like to combine these ratings in one column, however i am struggling with the power query formula. The formula in excel is =H1+I2 (I2 is offset horizontally down 1 cell). If the H1 Cell is say 7PM on the parent channel, the I2 cell is 8PM on the plus one channel (the 7PM programme on the parent channel delayed by one hour). I would like to find out the sum of what the programme did at real time + at delayed time if that makes sense?

    I know this may be really easy but i cannot figure out how to do such a fuction within powerquery and would love your help. Sorry if i haven't explained things fully, i don't want to over complicate things!

    Thanks in Advance!

    Wednesday, December 17, 2014 11:27 PM

Answers

  • One approach would be to create a custom column with a list of the Plus One channel ratings offset by one.

    1) First create an index column - Add Column-->Add Index Column. The default step name is #"Added Index" and the default column name is Index.

    2) Next create a custom column with the following formula:

    List.Skip(#"Added Index"[Plus One Channel Rating]){[Index]}?

     Substitute the real name of the Plus One channel rating column for the one used in the formula. Note: A list of the values in a column is created automatically when you combine a table name with a column name, so #"Added Index"[Plus One Channel Rating] is a list of all the values in the Plus One channel rating column. List.Skip skips the first value (unless you specify a different value in the optional second parameter), so that the channel rating values are offset by one. We use the value in the index column {[Index]} to return a rating value for each row. Because the offset list has one value less than the original list, our index lookup will fail for the last row in the table. Usually an error would be generated for the last row. However by using an index lookup in the form {[Index]}?, if there's no match then null is returned.

    3) Finally, simply add the rating from the "real-time" rating column to the rating in new Plus One rating "offset" column.

    Friday, December 19, 2014 4:46 PM

All replies

  • One approach would be to create a custom column with a list of the Plus One channel ratings offset by one.

    1) First create an index column - Add Column-->Add Index Column. The default step name is #"Added Index" and the default column name is Index.

    2) Next create a custom column with the following formula:

    List.Skip(#"Added Index"[Plus One Channel Rating]){[Index]}?

     Substitute the real name of the Plus One channel rating column for the one used in the formula. Note: A list of the values in a column is created automatically when you combine a table name with a column name, so #"Added Index"[Plus One Channel Rating] is a list of all the values in the Plus One channel rating column. List.Skip skips the first value (unless you specify a different value in the optional second parameter), so that the channel rating values are offset by one. We use the value in the index column {[Index]} to return a rating value for each row. Because the offset list has one value less than the original list, our index lookup will fail for the last row in the table. Usually an error would be generated for the last row. However by using an index lookup in the form {[Index]}?, if there's no match then null is returned.

    3) Finally, simply add the rating from the "real-time" rating column to the rating in new Plus One rating "offset" column.

    Friday, December 19, 2014 4:46 PM
  • Thank you Ed, this is exactly the solution that I was looking for. In my case, I wanted the previous row value  for calendar to fiscal year conversion (Fiscal value = current year's vale * x% + previous year's value * y%)

    I started my index at -1 with +1 increment and used the following formula get previous years value at current row

    List.Skip(#"Added Index"[Value],0){[Index]}?

    This worked however I am getting "Error" for the first row with {[Index]}? instead of null as suggested in your post

    Here is error details:

    Expression.Error: The index must not be negative.
    Details:
        Value=List
        Index=-1

    Do you have any suggestion on how to take care of "error" mentioned above?

    Please let me know if you have a better or different way to get previous row value in current row

    Thanks, Anil

    Friday, May 8, 2015 8:58 PM
  • Hi,

    I tried following your instructions with a table i have. I have a column called DateText in a table created by my powerquery pq_NELFT. There is also an Index column inserted.

    I want to add a column called DateAmend which will contain the DateText values but offset by 7 rows i.e. shifted down 7 rows.

    I tried initially  List.Skip(#"Added Index"[DateText]){[Index]}?

    This produces the error:

    Expression.Error: The column 'DateText' of the table wasn't found.
    Details:
        DateText

    And the custom step is recorded as:

    = Table.AddColumn(#"Filtered Rows4", "Custom", each List.Skip(#"Added Index"[DateText]){[Index]}?)

    Any idea what is going wrong please?

    Q

    Wednesday, May 25, 2016 2:45 PM
  • Hi,

    I found that the answer was that i wasn't doing the List.Skip step immediately after the add index step. When consecutively executed works like a charm.

    Many thanks,

    Q


    QHarri

    Thursday, May 26, 2016 7:19 AM