Power Query: parámetros dinámicos

Un usuario Pregunto ✅

Tryan

Hola,

Creé una consulta en Power Query que me permite escribir ciertos parámetros en una tabla en una hoja de cálculo de Excel que pasa dinámicamente a la consulta. Esta consulta extrae datos de una base de datos SQL. El código es:

dejar
Portafolio = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»Portfolio»],
Fecha = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»Date»],
Valores = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»Securities»],
MIS = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»MIS»],
Fuente = Sql.Database («Phlsql14clus01», «DW»,
[Query=»exec [dbo].[SSRS_Holdings_Asof] ‘»
& Number.ToText (cartera)
& «‘,'»
& Fecha
& «‘,'»
Y valores
& «‘,'»
& MIS
& «‘»])
en
Fuente

Esto funciona perfectamente bien para un conjunto de parámetros. Sin embargo, quiero pasar varios conjuntos de parámetros a la consulta a la vez. Por ejemplo, en el código anterior, puede ver que mis parámetros son «Cartera», «Fecha», «Valores» y «MIS». Si deseo extraer datos para la cartera 2155 a la fecha 29/12/17 para todos los valores y códigos MIS, puedo establecer esos parámetros en mi tabla de Excel y Power Query extraerá los datos. A continuación se muestra una imagen de esos parámetros en mi tabla de Excel:

Capture.PNG

Me gustaría poder agregar más filas a esta tabla para poder ejecutar una consulta para extraer datos de otros números de cartera específicos u otras fechas para la misma cartera (es decir, la fila 3 en la imagen podría tener la Cartera 2155 con la Fecha 11 / 30/17) todo en una sola consulta. He hecho esto con éxito manualmente con una consulta que escribe especialmente cada conjunto de parámetros y luego los agrega con algo como:

dejar
Source1 = Sql.Database («Phlsql14clus01», «DW», [Query=»exec [dbo].[SSRS_Holdings_Asof] «» 2155 «», «» 29/12/17 «», «» * «», «» * «» «]),
Source2 = Sql.Database («Phlsql14clus01», «DW», [Query=»exec [dbo].[SSRS_Holdings_Asof] «» 2155 «», «» 30/11/17 «», «» * «», «» * «» «]),
Source3 = Sql.Database («Phlsql14clus01», «DW», [Query=»exec [dbo].[SSRS_Holdings_Asof] «» 2155 «», «» 31/10/17 «», «» * «», «» * «» «]),
Source4 = Sql.Database («Phlsql14clus01», «DW», [Query=»exec [dbo].[SSRS_Holdings_Asof] «» 2155 «», «» 30/09/17 «», «» * «», «» * «» «]),

# «Consulta adjunta» = Table.Combine ({Source1, Source2, Source3, Source4})

en
# «Consulta adjunta»

Donde cada fuente tiene un conjunto específico de parámetros. Pero me gustaría lograr ese tipo de anexos usando la tabla en Excel para alimentar la primera consulta en mi publicación. También quiero que sea lo suficientemente dinámico como para poder agregar tantas filas en la tabla que desee (es decir, sin embargo, muchos conjuntos diferentes de parámetros que quiero consultar en mis datos).

Si alguien sabe cómo hacerlo, ¿puede ofrecer modificaciones que pueda realizar en la primera consulta de mi publicación?

En respuesta a Tryan

Hola @tryan,

Tal vez puedas intentar usar la siguiente fórmula.

Ejemplo de función personalizada:

let 
	GetData=(index as number)=>
let

        Datasource=Excel.CurrentWorkbook(){[Name="param"]}[Content],//source
        //paramtere
        Portfolio=Datasource{index}[#"Portfolio"],
        Date=Datasource{index}[#"Date"],
        Securities=Datasource{index}[#"Securities"],
        MIS=Datasource{index}[#"MIS"],
        //invoke
        Source = Sql.Database("Phlsql14clus01", "DW",
        [Query="exec [dbo].[SSRS_Holdings_Asof] '"
        & Number.ToText(Portfolio)
        & "', '"
        & Date
        & "', '"
        & Securities
        & "', '"
        & MIS
        & "'"])

in
    Source
in
    GetData

Cree una tabla con índice para invocar una función personalizada.

9.PNG

Saludos,

Xiaoxin Sheng

Hola @tryan,

>>Sin embargo, quiero pasar varios conjuntos de parámetros a la consulta a la vez.

No creo que sea simplemente para lograr. En mi opinión, me gustaría sugerirle que almacene estas conexiones en otro archivo y use Power query para cargar datos.

Después de finalizar la carga, puede escribir e invocar la función personalizada para recorrer la tabla anterior para obtener datos de otras fuentes.
Luego combínalos en una sola tabla.

Ejemplo de parámetro de entrada de función personalizada y tabla de retorno:

let
    LoadData=(portfolio as text , date as text,optional securities as text, optional mis as text) as table =>
    let
        Security = if securities <> null then securities else "*",
        MIS = if mis <> null then mis else "*",
        Source = Sql.Database("Phlsql14clus01", "DW", [Query="exec [dbo].[SSRS_Holdings_Asof] " &portfolio& "," &date& ","&Security&","&MIS])
    in
        Source
in 
    LoadData

Saludos,

Xiaoxin Sheng

Tryan

En respuesta a v-shex-msft

Eso no parece funcionar muy bien para lo que quiero. Aunque encontré algo nuevo. Los ceros rojos resaltados a continuación son los que controlan qué fila de la tabla ingresa en la consulta.

dejar

Portafolio = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»Portfolio»],
Fecha = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»Date»],
Valores = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»Securities»],
MIS = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{0}[#»MIS»],
Fuente = Sql.Database («Phlsql14clus01», «DW»,
[Query=»exec [dbo].[SSRS_Holdings_Asof] ‘»
& Number.ToText (cartera)
& «‘,'»
& Fecha
& «‘,'»
Y valores
& «‘,'»
& MIS
& «‘»])

en
Fuente

Entonces, por ejemplo, si quiero extraer un conjunto de parámetros de la segunda fila de mi tabla de parámetros (mi publicación original tiene una imagen de cómo se ve la tabla de parámetros), cambio los 0 a 1. Si quiero la tercera fila, cambio los 0 a 2, y así sucesivamente. Quiero editar el código a algo como a continuación. Crearía algún tipo de bucle basado en un recuento del número de filas en mi tabla de parámetros. Entonces, si tengo diez filas en mi tabla de parámetros, me pregunto si puedo usar algo como List.Generate y table.RowCount para crear una lista de 0 a 9. Luego pasaría cada número a las i de abajo, y agregaría cada una de las 10 consultas diferentes en una tabla. ¿Alguien puede ayudarme a crear algo así?

dejar

Portafolio = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{I}[#»Portfolio»],
Fecha = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{I}[#»Date»],
Valores = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{I}[#»Securities»],
MIS = Excel.CurrentWorkbook () {[Name=»param»]}[Content]{I}[#»MIS»],
Fuente = Sql.Database («Phlsql14clus01», «DW»,
[Query=»exec [dbo].[SSRS_Holdings_Asof] ‘»
& Number.ToText (cartera)
& «‘,'»
& Fecha
& «‘,'»
Y valores
& «‘,'»
& MIS
& «‘»])

en
Fuente

En respuesta a Tryan

Hola @tryan,

Tal vez puedas intentar usar la siguiente fórmula.

Ejemplo de función personalizada:

let 
	GetData=(index as number)=>
let

        Datasource=Excel.CurrentWorkbook(){[Name="param"]}[Content],//source
        //paramtere
        Portfolio=Datasource{index}[#"Portfolio"],
        Date=Datasource{index}[#"Date"],
        Securities=Datasource{index}[#"Securities"],
        MIS=Datasource{index}[#"MIS"],
        //invoke
        Source = Sql.Database("Phlsql14clus01", "DW",
        [Query="exec [dbo].[SSRS_Holdings_Asof] '"
        & Number.ToText(Portfolio)
        & "', '"
        & Date
        & "', '"
        & Securities
        & "', '"
        & MIS
        & "'"])

in
    Source
in
    GetData

Cree una tabla con índice para invocar una función personalizada.

9.PNG

Saludos,

Xiaoxin Sheng

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *