Forum Discussion

Manuel475's avatar
Manuel475
Copper Contributor
Aug 03, 2021
Solved

POWER QUEREY FILTER

Hi there,   I'm trying to make a filter like this:       let Origen = Excel.Workbook(File.Contents("\Ubicaciones.xlsx"), null, true), Ubicaciones_Table = Origen{[Item="Ubicaciones",Kin...
excel
Office Scripts
  • Manuel475's avatar
    Manuel475
    Aug 03, 2021

    I got the answer,

     

    As we can read https://social.technet.microsoft.com/Forums/sqlserver/en-US/98888d04-8543-4acc-9e76-5983cea80c86/dynamic-named-range-not-recognized-by-excel-2016-power-query  is not possible to do directly, bu we can fix it.

     

    The result is here:

     

    let
    Origen = Excel.Workbook(File.Contents("\Ubicaciones.xlsx"), null, true),
    Ubicaciones_Table = Origen{[Item="Ubicaciones",Kind="Table"]}[Data],
    #"Tipo cambiado" = Table.TransformColumnTypes(Ubicaciones_Table,{{"Id", Int64.Type}, {"Name", type text}, {"IdProject", type text}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([IdProject] = Excel.CurrentWorkbook(){[Name="code"]}[Content]{0}[Column1]))
in
    #"Filas filtradas"

     

    Many thanks.

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 03, 2021

Manuel475 

What is the function proyecto.getValue() ? Perhaps it shall be simply

#"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([IdProject] = proyecto))

 

  • Manuel475's avatar
    Manuel475
    Copper Contributor
    Aug 03, 2021
    Hi @Sergei,

    The point is, I'm using power query in Excel, and I need to use the value on cell "B1" on sheet "hoja1" to do the filter there.
    So I tried to read the value on that cell and storage on the variable "proyecto", but sure is not the right way.
    Is it possible?

    Thank you in advance.
    • Manuel475's avatar
      Manuel475
      Copper Contributor
      Aug 03, 2021

      I got the answer,

       

      As we can read https://social.technet.microsoft.com/Forums/sqlserver/en-US/98888d04-8543-4acc-9e76-5983cea80c86/dynamic-named-range-not-recognized-by-excel-2016-power-query  is not possible to do directly, bu we can fix it.

       

      The result is here:

       

      let
    Origen = Excel.Workbook(File.Contents("\Ubicaciones.xlsx"), null, true),
    Ubicaciones_Table = Origen{[Item="Ubicaciones",Kind="Table"]}[Data],
    #"Tipo cambiado" = Table.TransformColumnTypes(Ubicaciones_Table,{{"Id", Int64.Type}, {"Name", type text}, {"IdProject", type text}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([IdProject] = Excel.CurrentWorkbook(){[Name="code"]}[Content]{0}[Column1]))
in
    #"Filas filtradas"

       

      Many thanks.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 03, 2021

        Manuel475 

        I'd still use

        p = Excel.CurrentWorkbook(){[Name="code"]}[Content]{0}[Column1])

        within current query and after that "p" in filter. Much easier in maintenance.

Resources