Forum Discussion
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",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.
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.
4 Replies
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.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.
