none
PQ Equivalent to VBA (.Offset) Property RRS feed

  • Question

  • Hi All,

    Sorry to be posting another "theoretical" question here.

    I have the following dilemma:

    • Working with a column of Tables
    • Need to "reach into" each Table, and find certain text, in a certain column ("Search Column")
    • Utilizing List.FindText against the "Search Column" results in a List of the results (say, 7 instances out of 50 total rows)
    • This works OK, except, I need to pull in the values (for those 7 rows), from a different column ("Results Column")
    • In VBA I would use the .Offset(0, 3) property.  But I am stuck here.
    • My only path forward I see if to create another column of tables somehow with those 7 rows, and then work from there.

    Ideas?  Thank you all!

    Jake

    Friday, October 27, 2017 11:23 PM

Answers

  • What you can do is get the positions for the matches, and use the positions to get the corresponding values from the result column:

    SearchList = List.Buffer(<tablename>[Search Column]),
    ResultList = List.Buffer(<tablename>[Result Column]), SearchPositions = List.PositionOfAny(SearchList, List.FindText(SearchList, "SearchValue"), Occurrence.All)
    ResultValues = List.Transform(SearchPositions, each ResultList{_})

    • Marked as answer by Jake Burns Monday, October 30, 2017 9:40 PM
    Saturday, October 28, 2017 12:30 AM

All replies

  • What you can do is get the positions for the matches, and use the positions to get the corresponding values from the result column:

    SearchList = List.Buffer(<tablename>[Search Column]),
    ResultList = List.Buffer(<tablename>[Result Column]), SearchPositions = List.PositionOfAny(SearchList, List.FindText(SearchList, "SearchValue"), Occurrence.All)
    ResultValues = List.Transform(SearchPositions, each ResultList{_})

    • Marked as answer by Jake Burns Monday, October 30, 2017 9:40 PM
    Saturday, October 28, 2017 12:30 AM
  • Whoa,

    Cool.  Thank you Colin.

    Monday, October 30, 2017 9:29 PM