Solución:
No estoy de acuerdo con la premisa de la respuesta anterior; VBA es absolutamente adecuado para automatizar PowerQuery y es particularmente eficiente para trabajos repetitivos. El truco consiste en crear primero la consulta que necesita en PowerQuery, luego usar el Editor avanzado para capturar el M. Cópielo y guárdelo, ya sea en una celda del libro de trabajo o en un archivo de texto separado.
Gil Raviv describe en detalle el método. Para mayor comodidad, guardo mi M en archivos de texto en lugar del libro de trabajo y lo cargo con:
Function LoadTextFile(FullFileName As String) As String
With CreateObject("Scripting.FileSystemObject")
LoadTextFile = .OpenTextFile(FullFileName, 1).readall
End With
End Function
Lo bueno de los archivos de texto es que son independientes de Excel y pueden ser reutilizados por muchos libros de trabajo.
Aquí hay algo de M:
let
// load the reference file (variables are shown in capitals;
// variable values are replaced with strings from the excel control workbook)
Source = Excel.Workbook(File.Contents(PATH_AND_NAME), null, true),
ImportSheet = Source{[Item=SHEET_NAME,Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(ImportSheet),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACCOUNT", type text}})
in
#"Changed Type"Una vez cargado en VBA (ya sea desde el libro de trabajo o el archivo de texto), la M se puede editar dentro de VBA, por ejemplo, sustituyendo palabras de marcador de posición, o usando los nombres de los comandos M para ubicar y alterar líneas si es necesario, p.
' create the M script to read the M file that will do the import
M_Script = LoadTextFile(M_Source)
' insert the path
M_Script = Replace(M_Script, "PATH_AND_NAME", """" & qSource & """")
' insert the worksheet name
If wksName <> "" Then M_Script = Replace(M_Script, "SHEET_NAME", """" & wksName & """")
El siguiente paso es cargar la consulta. Hago esto usando la técnica descrita por Gil de la siguiente manera:
Dim qry As WorkbookQuery
If DoesQueryExist(qName) Then
' Deleting the query
Set qry = ThisWorkbook.Queries(qName)
qry.Delete
End If
Set qry = w.queries.Add(qName, M_Script, qSource)
' We check if data should be loaded to Data Model
shouldLoadToDataModel = ThisWorkbook.Worksheets(1).Cells(13, "D")
' We check if data should be loaded to worksheet
shouldLoadToWorksheet = ThisWorkbook.Worksheets(1).Cells(13, "E")
If shouldLoadToWorksheet Then
' We add a new worksheet with the same name as the Power Query query
Set currentSheet = Sheets.Add(After:=ActiveSheet)
currentSheet.Name = qName
If Not shouldLoadToDataModel Then
' Let's load to worksheet only
LoadToWorksheetOnly qry, currentSheet
Else
' Let's load to worksheet and Data Model
LoadToWorksheetAndModel qry, currentSheet
End If
ElseIf shouldLoadToDataModel Then
' No need to load to worksheet, only Data Model
LoadToDataModel qry
End If
El código de Gil permite importar datos al modelo de datos o a una hoja de trabajo. El OP requiere el segundo, y si se sigue el método, los datos transformados deberían aparecer en la hoja de trabajo.
WIP:
Entonces, ¿cómo escribir esto para que sea suficiente? La conclusión es que debe configurar su consulta utilizando las herramientas integradas, no VBA. Carga sus datos a través del método apropiado, que puede ser desde un archivo, repitiendo archivos en una carpeta, web, base de datos … la lista continúa. Puede importar desde fuentes externas así como cargar desde internas. Eche un vistazo aquí para obtener más información sobre la carga desde fuentes externas.
Una vez que haya asegurado su fuente y esté cargada, se le presentará el editor de consultas donde puede realizar sus pasos de transformación.
El punto es que a medida que realiza sus pasos utilizando la interfaz de usuario, el código M se escribe en segundo plano y forma la base de una consulta reutilizable siempre que no cambie el formato de origen o la ubicación.
En su caso, cuando haya realizado sus pasos y tenga una consulta como desee, cierre y cargue en sheet2.
En este paso, la primera vez que lo configure, seleccionará la hoja 2 como su destino de cierre y carga:
NB: Cuando seleccione la hoja existente, asegúrese de que la Hoja 2 ya exista y de que puede editar la Hoja2 manualmente. delante del rango sugerido.
Estás experimentando problemas porque sigues intentando recrear todo esto con código.
No lo hagas. Configúrelo usando la interfaz de usuario y cárguelo en sheet2. A partir de ese momento, abra el editor de consultas para editar los pasos y / o actualice la consulta para cargar la hoja existente2 con datos nuevos / actualizados.
Algunos de los métodos disponibles para actualizar su consulta:
La consulta se actualizará mediante VBA / Actualizaciones manuales en la hoja en la que reside (Hoja2) o en el libro de trabajo en sí, por ejemplo Sheet2.Calculate , ThisWorkbook.RefreshAll, presionando manualmente el botón actualizar libro de trabajo en la pestaña de datos (todos estos son realmente exagerados)
Métodos más específicos:
VBA para la tabla de consulta en la hoja 2:
ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
Cambie lo anterior a la tabla apropiada, etc.
Haga clic derecho en la propia tabla de consultas y seleccione actualizar:
Haga clic en el botón actualizar en la ventana de consultas del libro de trabajo en el lado derecho de la consulta en cuestión (icono con flechas circulares verdes)
The Ken Pulls VBA way (edición menor de mí)
Option Explicit
Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
On Error GoTo 0
End Sub
No debería haber ninguna necesidad real de que hagas todo este trabajo a través de VBA. Es posible que tenga una manipulación de datos complicada con la que se sienta más cómodo con VBA y luego tenga acceso a PowerQuery que procesó los datos como fuente. Puede disparar todo al tener una subrutina que llame a la rutina de procesamiento y luego use uno de los métodos de comando de VBA enumerados anteriormente. Hay más métodos y los agregaré cuando tenga más tiempo.
Cálculos:
Si tiene cálculos que dependen de la salida de PowerQuery, tiene 4 opciones obvias inmediatas:
- Agregue estos cálculos cuando sea posible en PowerQuery. Admite columnas calculadas, funciones definidas por el usuario y mucho más.
- Agregue la salida de PowerQuery al modelo de datos y use el modelo de datos para realizar cálculos, incluidos los campos calculados. Esto también le dará acceso a funciones de inteligencia de tiempo.
- Use VBA para agregar los cálculos a las áreas apropiadas en la hoja 2 si el rango cambia al actualizar
- Si el rango no cambia en la actualización, simplemente elimine las fórmulas.
Un poco tarde para esta conversación, pero me resultó bastante fácil actualizar una Power Query en VBA editando la propiedad Formula.
Primero actualice el let..in fórmula. Luego actualice las conexiones que lo usan
Dim pqFormula as String
pqFormula = "let..in"
Dim pqName as String
pqName = "<Name of the connection>"
' Update the formula for the specific power query.
ThisWorkbook.queries(pqName).Formula = pqFormula
' Refresh the connection
ThisWorkbook.Connections("Query - " & pqName).Refresh
Todas las tablas que descansan en esa conexión deben actualizarse de inmediato.
Saludos,