Obtenga el tipo adecuado de la función PowerQuery

[Lucian]

Hola

Necesito ayuda para obtener el tipo adecuado dentro de una función PowerQuery.

Para obtener algunos datos de una tabla de Navision, he creado una función:

(strCompanyName as text) as table =>
let
    Source = DynamicsNav.Contents(NAVServiceURL, null),
    SelectCompany = Source{[Name=strCompanyName]}[Data],
    BI_G_L_Entry_table = SelectCompany{[Name="BI_G_L_Entry",Signature="table"]}[Data]
in
    BI_G_L_Entry_table

Esta función se usará en una segunda consulta para obtener datos de tablas similares para varias empresas de esa base de datos de Navision.

Al invocar la función para probarla, se devolverán datos de esa tabla con los tipos adecuados: Texto, Entero, Fecha/Hora/Zona horaria.

Pero al invocar la función en otra consulta y expandir la parte de la tabla resultante no tendrá los tipos adecuados.

La consulta es la siguiente:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", {"Entry_No", "G_L_Account_No", "Posting_Date", "Document_Type", "Document_No", "Debit_Amount", "Credit_Amount", "Amount"})
in
    #"Expanded fxBI_G_L_Entry"

¿Hay alguna manera de "propagar" tipos directamente en el paso con "Table.ExpandTableColumn" sin addind en la última consulta un paso "Cambiar tipo" para dar formato adecuado a cada columna?

Atentamente

Lucian

[v-shex-msft]

Hola @Lucian,

Tal vez pueda usar Table.ColumnNames para dirigir el nombre de columna de extracción de los campos de columna que almacenan los valores de tabla:

let
    Source = fxBI_CompanyName(),
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxBI_G_L_Entry", each fxBI_G_L_Entry([NAV Company Name])),
    #"Expanded fxBI_G_L_Entry" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBI_G_L_Entry", Table.ColumnNames(#"Invoked Custom Function"[fxBI_G_L_Entry]{0}))
in
    #"Expanded fxBI_G_L_Entry"

saludos

Xiaoxin Sheng

[Lucian]

Hola @v-shex-msft ,

Y gracias por su sugerencia. Desafortunadamente, el comando "Table.ColumnNames"sería útil solo para expandir todos los nombres de columna de esa función de tabla (en lugar de una "lista corta" de ellos), pero no conservó los tipos de columna.

Todos ellos son de "cualquier tipo" (ABC123) en lugar de DateTime od Número por lo que tengo que hacer un "tipo de cambio" para ellos.

¿Alguna otra ideea? No entiendo por qué invocar la función por separado dará los resultados adecuados, pero cuando se usa dentro de otra consulta devolverá datos genéricos.

Atentamente

Lucian

[v-shex-msft]

Hola @Lucian,

¿Puede compartir algunos datos ficticios con una estructura de datos similar para la prueba a la fórmula de codificación? Será de ayuda para la fórmula de codificación y hacer más pruebas.

Cómo obtener respuesta rápida a su pregunta

saludos

Xiaoxin Sheng

[Lucian]

Hola @v-shex-msft ,

Lo sentimos por retraso: tomó algún tiempo preparar un entorno de prueba 🙂

Por lo tanto, he creado una base de datos SQL de Azure en un inquilino de prueba, por lo que solo estará disponible en los próximos 30 días.

SQL Server: tst-pbi-2003.database.windows.net

Nombre de la base de datos: PBI-2003 (es la base de datos de ejemplo AdventureWorksLT) y "duplicado" la tabla Product para simular las múltiples empresas de una base de datos Navision: COMP1$Products y COMP2$Products.

Para acceder a los datos el usuario es NAVUser y la contraseña es Pa55w.rd (contraseña de laboratorio de Microsoft habitual)

La tabla "empresas"he introducido datos directamente en el archivo PBIX

#table({"NAV Column Name"}, {{"COMP1"}, {"COMP2"}})

Para reproducir el entorno de prueba lo más cerca posible, para obtener la tabla Producto he creado la función:

(strCompName as text) as table =>
let
    Source = Sql.Database(NAVServer, NAVDBName),
    #"ProductTable" = Source{[Schema="SalesLT",Item=strCompName&"$Product"]}[Data]
in
    #"ProductTable"

A continuación, para obtener todo el producto para todas las empresas enumeradas en la tabla Empresas, he utilizado su enfoque:

let
    Source = Companies,
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxProductTable", each fxProductTable([NAV Column Name])),
    #"Expanded fxProductTable" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProductTable", Table.ColumnNames(#"Invoked Custom Function"[fxProductTable]{0}))
in
    #"Expanded fxProductTable"

Al probar la función y la entrada manualmente para COMP1 tengo los resultados propoer:

Pero en la línea "Expanded fxProductTable" obtengo:

El archivo PBIX completo está aquí: https://1drv.ms/u/s!AusSzc3evzopaBXA-XlLaxxVlNM?e=5f1XzU

Gracias por su ayuda.

Atentamente

Lucian

[v-shex-msft]

HI @Lucian,

Probé con algunas funciones de consulta M, pero no se ha anidado el parámetro de tipo de cambio en la función de columna en expansión. Parece que solo puede extraer el esquema de tabla anterior y usarlo en los siguientes pasos de tipo de cambio.

Función personalizada para extraer el nombre de columna de tabla y la estructura de tipo que se puede utilizar en la función de tipo de modificación:

let
    RecognizeType=(tb as table) => List.Zip({ Table.Schema(tb)[Name],List.Transform(Table.Schema(tb)[Kind], each Expression.Evaluate("type "&_))})
in
    RecognizeType

Uso:

#"Changed Type"= Table.TransformColumnTypes(#"previous steps",RecognizeType('table'))

Aviso:

1. Por favor, haga máscara con datos confidenciales compartidos.

saludos

Xiaoxin Sheng

[Lucian]

Hola @v-shex-msft ,

Y gracias por tu sugestión y paciencia.

He probado su función en la consulta de "consolidación" como esta:

let
    Source = Companies,
    #"Invoked Custom Function" = Table.AddColumn(Source, "fxProductTable", each fxProductTable([NAV Column Name])),
    #"Expanded fxProductTable" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProductTable", Table.ColumnNames(#"Invoked Custom Function"[fxProductTable]{0})),
    #"Changed Type"= Table.TransformColumnTypes(#"Expanded fxProductTable",RecognizeType(REF_SalesLT_COMP1Product))
in
    #"Changed Type"

Donde "REF_SalesLT_COMP1Product" es la tabla de "referencia" para una sola empresa desde donde la función debe obtener el tipo correcto para cada colum. Si este fue el uso indended de su función.

Desafortunadamente, de esta manera recibo un mensaje de error:

Formula.Firewall: la consulta 'CONSOLIDATED_Products_v3' (paso 'Tipo modificado') hace referencia a otras consultas o pasos, por lo que es posible que no acceda directamente a un origen de datos. Vuelva a generar esta combinación de datos.

La buena noticia es que si configuro el informe a "Ignorar los niveles deprivacidad y potencialmente mejorarel rendimiento" parece ser exaaactly lo que necesito! Así que, ¡tengo que agradecerte por esta ideea! 😀

Pero... ¿Es posible evitar el error Formula.Firewall y utilizar la opción predeterminada"Combinar datos de acuerdo con la configuración de su nivel de privacidad para cada fuente"?

Atentamente

Lucian

[v-shex-msft]

HI @Lucian,

Este problema puede deberse a force invoke external o anterior steps datasource para calcular en el paso actual. Pueden afectar a la tabla de cálculos y causar los problemas de conflicto o compatibilidad cuando se calculan fuera del paso y el contenido actuales.

Puedes echar un vistazo al siguiente blog para saber más sobre este problema:

Privacidad de datos y el firewall de fórmulas en Power BI / Power Query

saludos

Xiaoxin Sheng

[Lucian]

Hola @v-shex-msft ,

Gracias por su apoyo! 😁

Atentamente

Lucian