Forum Discussion

ajay_gajree's avatar
ajay_gajree
Helper I
5 years ago
Solved

Power Query M Code - Select Columns if the Header contains two specific values

= Table.SelectColumns(Source,{"51a", "51b", "51c", "51d", "51e", "51f", "51g", "51h", "51i", "51k", "Entry Id"})

 

Hi all

 

I have the above M Step, is there a way to dynamically code this so that I pick up all column headers that contain 51 and additionally Entry Id?

 

Best regards

 

Ajay

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, ajay_gajree 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may add a new step with the following m codes.

    = Table.SelectColumns(
         #"Changed Type",
         List.Combine({
         {"Entry ID"},
         List.Select(
                Table.ColumnNames(#"Changed Type"),
                each Text.Contains(_,"51")
         )
         })
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Use Table.ColumnNames() to get the list and then remove everything from the list that doesn't match your requirements.  Then use the remaining list in the Table.SelectColumns() statement.

    • vic24he's avatar
      vic24he
      Frequent Visitor
      3 years ago

      How do you pass a list into table.selectcolumns? When I pass my list into the function it gets:

       

      Expression.Error: We cannot convert a value of type List to type Text.
      Details:
      Value=[List]
      Type=[Type]

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        Please show the relevant portion of the Power Query code.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, ajay_gajree 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may add a new step with the following m codes.

    = Table.SelectColumns(
         #"Changed Type",
         List.Combine({
         {"Entry ID"},
         List.Select(
                Table.ColumnNames(#"Changed Type"),
                each Text.Contains(_,"51")
         )
         })
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.