Forum Discussion

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

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

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

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

Copper Contributor

Aug 03, 2021

I got the answer,

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