SOLVED

POWER QUEREY FILTER

%3CLINGO-SUB%20id%3D%22lingo-sub-2604932%22%20slang%3D%22en-US%22%3EPOWER%20QUEREY%20FILTER%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2604932%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20a%20filter%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%0A%20%20%20%20Origen%20%3D%20Excel.Workbook(File.Contents(%22%5CUbicaciones.xlsx%22)%2C%20null%2C%20true)%2C%0A%20%20%20%20Ubicaciones_Table%20%3D%20Origen%7B%5BItem%3D%22Ubicaciones%22%2CKind%3D%22Table%22%5D%7D%5BData%5D%2C%0A%20%20%20%20hoja%20%3D%20workbook.getWorksheet(%22Hoja1%22)%2C%0A%20%20%20%20proyecto%20%3D%20hoja.getCell(%22B1%22)%2C%0A%20%20%20%20%23%22Tipo%20cambiado%22%20%3D%20Table.TransformColumnTypes(Ubicaciones_Table%2C%7B%7B%22Id%22%2C%20Int64.Type%7D%2C%20%7B%22Name%22%2C%20type%20text%7D%2C%20%7B%22IdProject%22%2C%20type%20text%7D%7D)%2C%0A%20%20%20%20%23%22Filas%20filtradas%22%20%3D%20Table.SelectRows(%23%22Tipo%20cambiado%22%2C%20each%20(%5BIdProject%5D%20%3D%20proyecto.getValue()))%0Ain%0A%20%20%20%20%23%22Filas%20filtradas%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20doesn't%20work%20the%20%22proyecto.getValue()%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomeone%20can%20help%20me%3F%2C%20it's%20my%20fist%20time%20I%20try%20to%20use%20a%20personalized%20filter%20in%20powerquery.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2604932%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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.

 

4 Replies

@Manuel475 

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

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

 

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.
best response confirmed by Manuel475 (New Contributor)
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.

 

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