none
Power Query function syntax: Add column in one table depending on value stored in second table RRS feed

  • Question

  • Hello,

    I have a rather simple issue using Power Query, I think.

    Table 1:
    Includes columns with amounts for different months (Column: Jan; Values: amounts for January, Column Feb; Values: amounts for February, ...).

    Table 2:
    Includes only one column with one record (Column: Current Month; Value: e.g. February).

    I would like to add a column (Year to Date) in Table 1. Which columns to add depends on the month stored in Table 2. So the funtion I am looking for is like this:

    If Table 2 value is January then Jan else

    If Table 2 value is February then Jan + Feb else

    ...

    If Table 2 value if December then Jan + Feb + Mar + ... + Dec.

    How is the syntax of the function?

    Thanks a lot!

    Best

    Christoph




    • Edited by Christoph Ku Thursday, February 16, 2017 10:29 AM
    Thursday, February 16, 2017 10:23 AM

Answers

  • That would be:

    let
        Source = Table1,
        #"Added Custom" = Table.AddColumn(Source, "Year to Date", each List.Sum(List.FirstN(Record.FieldValues(_),1+List.PositionOf(Record.FieldNames(_),Table.FirstValue(Table2)))))
    in
        #"Added Custom"

    Thursday, February 16, 2017 2:12 PM

All replies