Power Query: Recuperar datos en cualquier posición

Veremos en el día de hoy un ejercicio desarrollado con Power Query que nos permite recuperar información de una hoja (elegida a nuestra discreción) de un libro de trabajo; con la particularidad que cada hoja tiene los datos distribuidos por filas y columnas un tanto desordenados... Veamos la imagen:

Podemos observar que el único punto en común de las tres hojas es la existencia de cuatro campos iguales: Fecha, Cuenta, Debe y Haber; colocados en filas y columnas distintas...
Esto descartaría a priori la creación de una consulta con patrón único para las tres.

Pero veamos una posible solución al problema, empleando Table.FindText, List.PositionOf y Table.PositionOf.
Partiremos de una hoja donde tengamos una celda con validación de datos tipo lista que permita elegir entre una de las tres hojas de nuestro libro origen (celda B1 y nombre asignado 'ndPaís'), y una Tabla ('tblCampos') con los campos que nos interese recuperar de las distintas hojas (en el orden establecido).

Obviamenete lo primero que haremos será cargar al Editor de Power Query la tabla y la celda comentadas solo como conexión.
Lal tabla sin modificación alguna, y la celda 'ndPaís' que convertiremos en valor 'desagrupando datos'... o personalizando desde el editor avanzado hasta que quede:

let
    Origen = Excel.CurrentWorkbook(){[Name="ndPais"]}[Content]{0}[Column1]
in
    Origen

Y ahora desde el Editor de Power Query añadiremos una Consulta en blanco con el siguiente código:

let
    // vinculamos con el fichero fuente (con sus tres hojas:ES, FR e DE)
    Origen = Excel.Workbook(File.Contents("F:\excelforo\PQ_PositionofAny_origen.xlsx"), null, true),
    // filtramos por el valor de la celda 'ndPaís', según el nombre de la hoja
    FilasFiltrada_PAIS = Table.SelectRows(Origen, each ([Name] = pPais)),
    // nos quedamos solo con el campo 'Data' que expandimos a continuación
    OtrasColumnasQuitadas = Table.SelectColumns(FilasFiltrada_PAIS,{"Data"}),
    Expande_Data=Table.Combine(OtrasColumnasQuitadas[Data]),

    // Eliminamos columnas vacías o sin ningún dato
    LimpiaColumnas=
        Table.SelectColumns(Expande_Data, List.Select(Table.ColumnNames(Expande_Data), each List.NonNullCount(Table.ToColumns(Table.SelectColumns(Expande_Data, _)){0})>0)),
    // obtenemos la lista ordenada de los campos de la tblCampos
    CamposOrd=tblCampos[Lista_Campos],
    // identificamos la fila donde coinciden/intersectan los campos buscados con los existentes
    // empleamos List.Intersect
    RfL=List.Transform(Table.ToRows(LimpiaColumnas), each List.Intersect({CamposOrd,List.Select(_, each _ <> null)})),
    // List.PositionOf devuelve la posición
    FilaFecha=List.PositionOf(RfL,CamposOrd),

    // con Table.FindText encontramos y recuperamos la fila completa donde localice el texto buscado (Usuario:)
    FilaUsuario=Table.PositionOf(LimpiaColumnas,Table.FindText(LimpiaColumnas,"Usuario:"){0}),
    // Table.Range retorna las filas de una tabla discriminando filas fuera del rango de filas
    // para eliminar filas superiores es habitual emplear Table.Skip
    TablaFinal=Table.Range(LimpiaColumnas, FilaFecha,FilaUsuario-FilaFecha),

    // subimos encabezados y eliminamos filas sin datos
    EncabezadosPromovidos = Table.PromoteHeaders(TablaFinal, [PromoteAllScalars=true]),
    FilasBlancoEliminadas = Table.SelectRows(EncabezadosPromovidos, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

    // ... por si aacaso volvemos a lanzar la instrucción de eliminar columnas sin datos
    RelimpiaColumnas= Table.SelectColumns(FilasBlancoEliminadas, List.Select(Table.ColumnNames(FilasBlancoEliminadas), each List.NonNullCount(Table.ToColumns(Table.SelectColumns(FilasBlancoEliminadas, _)){0})>0)),

    // acabamos asignando tipo de datos a Fecha y seleccionamos las columnas elegidas en la tblCampos
    TipoCambiado = Table.TransformColumnTypes(RelimpiaColumnas,{{"Fecha", type date}}),
    SeleccionColumnas = Table.SelectColumns(TipoCambiado,CamposOrd)
in
    SeleccionColumnas

Aceptamos y cargamos sobre la hoja de cálculo... solo tendremos que elegir un país/hoja en la celda B1 y actualizar la consulta para mostrar los datos contenidos en dicha hoja.

De especial interes es la función M:
List.Intersect(lists as list, optional equationCriteria as any) as list
que devolvería una lista con los elementos que crucen.
Otra función relevante es: Table.ToRows(table as table) as list
que genera una lista de listas con cada fila de la tabla dada.
Sobre esta lista de listas-filas aplicamos List.Select:
List.Select(list as list, selection as function) as list
con el que recuperamos listas de elementos que cumplan el criterio.
En nuestro ejemplo:
List.Select(_, each _ <> null)
que aplica sobre cada lista de Table.ToRows.
Acabamos llamando a: List.PositionOf(list as list, value as any, optional occurrence as nullable number, optional equationCriteria as any) as any
que nos dice en qué posición de la lista dada se encuentra lo que buscamos...

En definitiva obtendremos listas por cada fila, solo cuando tenga dato, dejando fuera los elementos nulos.

Este paso es clave para identificar el inicio de nuestro encabezado...


Otro paso importante es el empleo de:
Table.FindText(table as table, text as text) as table
que devuelve una tabla con aquellas filas donde se encuentre el texto buscado...
En nuestro ejemplo, nos sirve para localizar la fila donde se encuentre el fin de los datos.. ya que sabemos que el texto 'Usuario:' indica el fin del informe.
Nos apoyamos además en Table.PositionOf(table as table, row as record, optional occurrence as any, optional equationCriteria as any) as any para conocer el número de fila:
FilaUsuario=Table.PositionOf(LimpiaColumnas,Table.FindText(LimpiaColumnas,"Usuario:"){0})
Fíjate que al añadir {0} al resultado de la tabla obtenemos un Registro, que es lo que pide la función Table.PositionOf

El resto de código esta explicado en los comentarios del editor avanzado y no supone dificultad alguna.

Empleando estas funciones de 'busqueda' (List.PositionOf, Table.PositionOf, Table.FindText y alguna otra comentada en este post) conseguimos localizar encabezados y fin de datos, independientemente de dónde se hallen... un éxito!