Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Slect Data from column name specified in different column

Hi all!
So I have and issue.

So
In SelectFromTable I have a table that I wish to filter.
In ColumntoSelect I have column name that needs filtering.

Now, im using Power query in Excel.
also I followed the M code from this article here:
https://powerusers.microsoft.com/t5/Building-Power-Apps/Convert-text-string-to-column-name/td-p/1670513
by @elnachota.
However im finding an erroor that My Value Im filtering out does not exists:

which is weird:


and here is my full Mcode:

 

 

let
    Source = Table.ExpandRecordColumn( Table.AddColumn( Table.FromList( {"Case1", "Case2", "Case3"}),"Record", 
each 
[
ColumnToSelect = 
if [Column1] = "Case1" then "[Column1]"
else if [Column1] = "Case2" then "[Column2]"
else "[Column3]",
SelectFromTable =  Query1]),"Record", {"ColumnToSelect","SelectFromTable"}),
    Custom1 = Table.AddColumn(Source,"Selected Columns", each 
      let
        tabla_de_la_linea    = [#"SelectFromTable"], 
        columna_para_filtrar = [#"ColumnToSelect"]
      in
        Expression.Evaluate(
          "Table.SelectRows(tabla_a_evaluar,each " & columna_para_filtrar & " <> A)", 
          [
            Table.SelectRows     = Table.SelectRows, 
            tabla_a_evaluar      = tabla_de_la_linea, 
            columna_para_filtrar = columna_para_filtrar
          ]
        )
  )
in
    Custom1

 

 

 

  • Hi, Anonymous try to replace first parameter of Expression.Evaluate with

    "Table.SelectRows(tabla_a_evaluar,each " & columna_para_filtrar & " <> ""A"")"

     

4 Replies

  • Hi, Anonymous try to replace first parameter of Expression.Evaluate with

    "Table.SelectRows(tabla_a_evaluar,each " & columna_para_filtrar & " <> ""A"")"

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      This worked!
      Thank you so so much,
      Now if you could why the double quotes worked when actualy double quating like this is equal to no quates at all?
      Thats what im not getting.

      • AlienSx's avatar
        AlienSx
        Super User

        2 quotation marks in a row withing a string (which is in between double quotes itself) gives you single quotation mark in string. If it were not for Expression.Evaluate you would write smth like

        Table.SelectRows(tbl, each [columnA] <> "A") e.g. with quotation marks. So we need to emulate this string in Expression.Evaluate. Try this in Advanced Editor

        let
            Source = {"double quotes", " ""double quotes "}
        in
            Source

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    Can I push it up?