POWER QUEREY FILTER

[Manuel475]

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",Kind="Table"]}[Data],
    hoja = workbook.getWorksheet("Hoja1"),
    proyecto = hoja.getCell("B1"),
    #"Tipo cambiado" = Table.TransformColumnTypes(Ubicaciones_Table,{{"Id", Int64.Type}, {"Name", type text}, {"IdProject", type text}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([IdProject] = proyecto.getValue()))
in
    #"Filas filtradas"

But doesn't work the "proyecto.getValue()".

Someone can help me?, it's my fist time I try to use a personalized filter in powerquery.

Many thanks.

[#2]

@Manuel475 

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

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

[#3]

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.

[#4 · Accepted solution]

I got the answer,

As we can read here  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.

[#5]

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