Anexar consultas dinámicamente en Power Query

[Alex_Ooi]

Hola a todos

Un pequeño trasfondo de esta solicitud:

- Las tablas de la carpeta de consulta "Sales Batch" se crean consultando mi base de datos Postgresql

- Debido a problemas de rendimiento, la mejor manera es desglosar las consultas en fragmentos como este en lugar de establecer una condición abierta en mi consulta como

DONDE la fecha > '2017-01-01'

Con esto, puedo prever que actualice las consultas en caso de que Sales_Y2020 sea demasiado grande y comience a dar problemas de rendimiento de nuevo. Por lo tanto, es posible que necesite desglosar las consultas más. Dicho esto, cuando anexe las tablas juntas en "Ventas" desde la carpeta de consulta "Otras consultas", necesito configurarlas manualmente según el cuadro rojo a continuación.

¿Hay alguna manera para que Power Query anexe dinámicamente estas tablas juntas en "Ventas" desde "Otras consultas"?

Gracias

[AlB]

Hola @Alex_Ooi

#sections puede funcionar para usted

Echa un vistazo a https://radacad.com/get-list-of-queries-in-power-bi

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar felicitaciones si los mensajes son útiles.

Salud

[Alex_Ooi]

Hey @AlB , gracias por la respuesta. Me las arreglé para llegar a donde quería estar, lo único es... Toda la mesa se quedó en blanco después de golpear close & apply.

[AlB]

Puede extraer la lista de consultas que le interesa usar #sections y, a continuación, crear la expresión completa en una cadena y usar

Expression.Evaluate

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar felicitaciones si los mensajes son útiles.

Salud

[Alex_Ooi]

Nunca he trabajado con Expression.Evaluate antes en Power Query. ¿Puedes compartir más conmigo? ¿Agrego este paso al final de mi consulta o algo así? He leído la documentación que me vinculó, pero todavía no entiendo cómo ayuda eso en la situación aquí. Cualquier guía detallada es muy apreciada. Muchas gracias ya, @AlB

[AlB]

En realidad no lo necesita Expression.Evaluate. Sería complicar las cosas innecesariamente. Puede usar #sections para obtener la lista de todas las consultas, filtrar las que no desea y, a continuación, combinar (anexar) las restantes para obtener el resultado final. En este ejemplo se seleccionan y anexan todas las consultas cuyo nombre comienza con "Table1":

let
    Source=#sections,
    Section1 = Source[Section1],
    #"Converted to Table" = Record.ToTable(Section1),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Name], "Table1")),
    res = Table.Combine(#"Filtered Rows"[Value])
in
    res

Por favor, marque la pregunta resuelta cuando haya terminado y considere dar felicitaciones si los mensajes son útiles.

Salud

[Alex_Ooi]

Tuve una respuesta de Reza, esto es lo que dijo:

"Este método solo funciona dentro del Editor de Power Query. los entornos de #shared y #sessions no están disponibles al cargar datos en Power BI."

Es una pena que la solución pueda funcionar, pero desafortunadamente aún no es lo que quiero.

[AlB]

tal vez @ImkeF, @Mariusz, @edhans, @Greg_Deckler puede proporcionar algo de sabiduría aquí

[ImkeF]

Hola @Alex_Ooi ,

en lugar de crear consultas únicas para cada una de ellas, cree una tabla con una fila para cada una de ellas.

A continuación, expanda la columna de resultados. No hay necesidad de añadir nada.

Comience desde una tabla con parámetros y agregue una columna que agarre los datos con una función.

[Alex_Ooi]

Hola @ImkeF después de intentarlo durante unas semanas, esto es lo que hice.

Este es el comienzo de la función personalizada:

(List) =>
let
Source = Table.Combine({
List
}),

Que intento invocar de una lista de consultas que quiero, y devolvió un error:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "Sales_Y2017-Y2018" to type Table.
Details:
    Value=Sales_Y2017-Y2018
    Type=[Type]

Así que cuando intenté trabajar con esto, funciona bien, pero aún así esta función no es dinámica, ya que todavía me requiere seleccionar los archivos, y editar la función cuando el número de archivos que quiero combinado sin ser el mismo con el número de variables que declaré a continuación.

(FileName1 as table, FileName2 as table, FileName3 as table) =>
let
    Source = Table.Combine({
   FileName1, FileName2, FileName3
  }),

¿Tiene algún consejo sobre cómo puedo seguir adelante? Gracias.

[ImkeF]

Hola @Alex_Ooi ,

la idea es deshacerse de toda la carpeta "Ventas por lotes" y hacer cada uno en una tabla. Si hay problemas de perfomance, puede usar Function.InvokeAfter para determinar una duración que debe esperarse antes de que ocurra la siguiente llamada:

let
    // To make it easy to paste here, I've included the function that's been called in the query itself
    // but this would probably be a function that you're going to define a function separately that replicates the queries for your Sales Batch queries (using 2 parameters: Start and End date) 
    myFunction = (myStart as date, myEnd as date) => {Number.From(myStart)..Number.From(myEnd)},
    
// Sample data, replace by a table of your own
Source = Table.FromRows(Json.Doc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),

// Function.InvokeAfter is optional, adjust duration if necessary
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Function.InvokeAfter(() => myFunction([Start], [End]), #duration(0,0,0,5)))
in
    #"Added Custom"

[Alex_Ooi]

Hola @ImkeF gracias por su respuesta.

Tengo un pequeño problema en el error de sintaxis en la solución que proporcionó para ver si podía tener sentido con mi(s) tabla(s). La línea infractora está abajo, con el mensaje de error de sintaxis, "Token Comma Expected":

Source = Table.FromRows(Json.Doc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),

Pero si te entiendo perfectamente, lo más probable es que no sea capaz de "hacer todo en una tabla" ya que me estoy conectando a mi base de datos usando varios scripts SQL ajustando los intervalos de fechas que cada uno de estos scripts debe tener en cuenta. Los problemas de rendimiento que resaltó en mi publicación inicialmente no estaban relacionados con Power BI, sino más bien con el de mi servidor. Por lo tanto, estoy seguro de que las manipulaciones de Power BI no ayudarían, ya que el problema de rendimiento reside en el propio origen de datos.

Soy consciente de que podría haber otras soluciones, como importar la tabla/vista que quiero de la base de datos, y habilitar el plegado de consultas para permitir que la manipulación tenga lugar en Power Query, pero eso demostraría demasiado trabajo de mi lado reinventando la rueda. Así que nunca he trabajado en otras soluciones antes.

De todos modos, como parte del proceso de aprendizaje, todavía estoy interesado en ver su solución. ¿Le importaría ayudarme con el error de sintaxis anterior? Muy apreciado como siempre.

Salud.

[ImkeF]

Hola @Alex_Ooi ,

lamento mucho lo del código roto. (Han insertado los comentarios después aquí en el foro, por lo que debe haber estropeado sth hasta allí)

let
    // To make it easy to paste here, I've included the function that's been called in the query itself
    // but this would probably be a function that you're going to define a function separately that replicates the queries for your Sales Batch queries (using 2 parameters: Start and End date) 
    myFunction = (myStart as date, myEnd as date) => {Number.From(myStart)..Number.From(myEnd)},
    
// Sample data, replace by a table of your own
    Source= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzJR0lY0N9QyMIJ1YHSc4CWc5CKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),

// Function.InvokeAfter is optional, adjust duration if necessary
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Function.InvokeAfter(() => myFunction([Start], [End]), #duration(0,0,0,5)))
in
    #"Added Custom"

Estoy feliz de ajustar su código para la base de datos si lo pega aquí. Voy a integrar los 2 parámetros que necesitará para la fecha de inicio y fin.

[Alex_Ooi]

Gracias @ImkeF. El código que proporcionó parece bastante prometedor.

El código que estoy usando para los valores que obtento de la base de datos es el siguiente. Aquí está un fragmento de la consulta Sales_Y2017-Y2018:

let
    Source = Value.NativeQuery(PostgreSQL.Database("MyHost", "DatabaseName"), "SELECT
    ""Site Name"", ""Booking ID"", ""Booking Date""
    // truncated for simplicity of presentation
    FROM dbo.""MyView""
    WHERE ""Booking Date"" >= '2017-08-30' AND ""Booking Date"" < '2019-01-01',
    null, [EnableFolding = false])
in
    Source

Nada cambia para el resto de la estructura en el resto de las consultas almacenadas en la carpeta "Sales Batch", excepto los parámetros definidos en la cláusula WHERE. Como se explicó anteriormente, no puedo establecer mi parámetro de script SQL en lo siguiente, ya que mi base de datos lamentablemente no tiene el ancho de banda/capacidad para ejecutar la consulta de esa manera.

WHERE ""Booking Date"" >= '2017-08-30'

Así que mis desarrolladores me aconsejaron que desglosara mis consultas en algunos lotes, por lo tanto, se creó "Sales Batch".

Esperamos tener noticias suyas pronto. ¡Gracias de nuevo!

[ImkeF]

Hola @Alex_Ooi ,

por favor intente esto:

let
// To make it easy to paste here, I've included the function that's been called in the query itself
// but this would probably be a function that you're going to define a function separately that replicates the queries for your Sales Batch queries (using 2 parameters: Start and End date) 
myFunction = (myStart as text, myEnd as text) => 

let
Source = Value.NativeQuery(PostgreSQL.Database("MyHost", "DatabaseName"), "SELECT
""Site Name"", ""Booking ID"", ""Booking Date""
// truncated for simplicity of presentation
FROM dbo.""MyView""
WHERE ""Booking Date"" >= ' " & myStart & "' AND ""Booking Date"" < ' " & myEnd & "'",
null, [EnableFolding = false])
in
Source
,

// Sample data, replace by a table of your own
Source= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzJR0lY0N9QyMIJ1YHSc4CWc5CKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
AddStartString = Table.AddColumn(#"Changed Type", "StartString", each Text.From(Date.Year([Start])) & "-" & Text.PadStart(Text.From(Date.Month([Start])),2,"0") & "-" & Text.PadStart(Text.From(Date.Day([Start])),2,"0")),
AddEndString = Table.AddColumn(AddStartString, "EndString", each Text.From(Date.Year([End])) & "-" & Text.PadStart(Text.From(Date.Month([End])),2,"0") & "-" & Text.PadStart(Text.From(Date.Day([End])),2,"0")),

// Function.InvokeAfter is optional, adjust duration if necessary
CallFunction = Table.AddColumn(AddEndString, "Custom", each Function.InvokeAfter(() => myFunction([StartString], [EndString]), #duration(0,0,0,5)))
in
CallFunction

Me sorprende un poco que esté estableciendo el parámetro "habilitar plegado" en false. (Pero tal vez esto sólo será ignorado..)

[Alex_Ooi]

Hola @ImkeF , siento mucho que después de luchar para limpiar el código del error de sintaxis, todavía no podía pasar a través de ver el resultado esperado. La línea infractora está a continuación, con un mensaje de error que muestra, "coma token esperada".

WHERE ""Booking Date"" >= ' " & myStart & "' AND ""Booking Date"" < ' " & myEnd & "'",

He probado muchas permutaciones de dónde poner las citas dobles en casi todos los lugares posibles y todavía tengo un error.

Para compartir con usted en cuanto a por qué deshabilité el plegado de consultas (a propósito), fue porque había tenido malas experiencias trabajando con este conector donde luego me imaginé una solución alternativa (al conectarme a ODBC). Sin embargo, después de que mi empresa haya migrado su alojamiento de AWS a GCP, ya no puedo conectar el origen de datos a través de ODBC por razones extrañas. Así que para evitar que este tipo de problemas sucedan en el futuro, tuve que deshabilitar ese plegado de consultas.

Entiendo que teóricamente al habilitar el plegado de consultas sería más útil, pero para el conector Postgres, no es el caso prácticamente. Sin embargo, todavía espero poder conectar esa base de datos a través de ODBC de alguna manera de nuevo; seguir siendo consulta-ious al respecto!

Gracias de nuevo, espero saber de usted en la corrección para que pudiera aprender la sintaxis!

Salud

Alex

[Alex_Ooi]

Hola @ImkeF gracias por su consejo. Estoy atrapado en dos partes tho;

1. ¿Qué debo hacer una vez que he creado un parámetro? Dejé todos los ajustes predeterminados y escribí "Sales_" como mi valor actual en los parámetros.
2. ¿Qué función(es) debo usar para crear una tabla con una fila para cada una de las tablas, ya que #sections no pueden hacer el truco por mí?

Estoy deseando saber de usted. Gracias.

[amitchandak]

@Alex_Ooi , consulte si esto puede ayudar a https://radacad.com/append-vs-merge-in-power-bi-and-power-query