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...
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

     

     

     

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)