Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

[Power Query] Check which column names equal a [Column] for a conditional column

Hi, I can´t figure out this. I´ve looked in the forum but didn´t found an answer either.

Given a table like this:

I´d like to check which [Year] equals to a column name in the table, and then multiply [Value] by that [Column] found.

For example, in the first row [Year] = 2017, so I´d like the conditional column to output the value in column [2017] by [Value]:

Can this be done?

 

Best regards.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Hi Anonymous 

     

    First add an Index Column Starting from 0

     

    Then you can use this Custom Column from Query Editor

     

    =let mycolumn=Text.From([Year]) in
Table.Column(#"Added Index",mycolumn){[Index]}*[Value]
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    SureAnonymous

     

    Let me tell you first that there is an even easier solution which doesn't even require to Add an Index Column.

    Sorry it didn't occur to me first

    Just Add this custom column. It should give you desired result

     

    =Record.Field(_,Text.From([Year]))*[Value]

    _  gets you the current row record. Second argument is the fieldname from which you want to fetch the value

    https://docs.microsoft.com/en-us/powerquery-m/record-field

     

     

     

8 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Hi Anonymous 

     

    First add an Index Column Starting from 0

     

    Then you can use this Custom Column from Query Editor

     

    =let mycolumn=Text.From([Year]) in
Table.Column(#"Added Index",mycolumn){[Index]}*[Value]
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks a lot for the help Zubair_Muhammad, that works great!

      Would you mind to explain a bit what is going on in your expression? i.e. does the table need to be sort by Category and Year?

      Best regards.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        SureAnonymous

         

        Let me tell you first that there is an even easier solution which doesn't even require to Add an Index Column.

        Sorry it didn't occur to me first

        Just Add this custom column. It should give you desired result

         

        =Record.Field(_,Text.From([Year]))*[Value]

        _  gets you the current row record. Second argument is the fieldname from which you want to fetch the value

        https://docs.microsoft.com/en-us/powerquery-m/record-field

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I´d be something like:

    = Table.AddColumn(#"Previous Step", "Calculated_Column", 
                      each if List.Contains(Table.ColumnNames(#"Previous Step"), [Year]) 
                      then [Capture that column]*[Value] else 0)

    I´m trying to use List.Buffer for the [Capture that column] part but not getting there (yet).

    I´ve also tried getting the name of the column using List.RemoveMatchingItems two times, the idea is like this:

    List.RemoveMatchingItems ({"Category", "Value", "Year", "2017", "2018"}, 
                          {"2017"}) 
equals
{"Category", "Value", "Year", "2018"}

    So you could do:

    List.RemoveMatchingItems ({"Category", "Value", "Year", "2017", "2018"}, 
                          List.RemoveMatchingItems ({"Category", "Value", "Year", "2017", "2018"}, 
                                                    {"2017"})
equals
    {"2017"}

    I´d just need to use that one item list as a record table reference, something like:

    = Table.AddColumn(#"Previous Step", "Calculated_Column", 
                    each if List.Contains(Table.ColumnNames(#"Previous Step"), [Year]) 
                    then [Text.Combine(
                                      List.RemoveMatchingItems(Table.ColumnNames(#"Previous Step"), 
                                                          List.RemoveMatchingItems(Table.ColumnNames(#"Previous Step"), 
                                                                                       [Year])))]
                              *[Value] 
                         else 0)