Expansión de varias tablas de datos con nombres de columna desconocidos

Gracias por esta solución y funciona perfectamente bien si usamos la obtención de datos de Excel, pero si obtenemos datos de la carpeta, entonces esto funciona demasiado lento. Tengo una carpeta con 1 excel (solo para probar guardé 1 pero habrá muchos archivos) y cuando apliqué este enfoque, fue demasiado lento cuando revisé la barra de estado y luego descubrí que estaba cargando más de 500 Mb de datos donde como el tamaño de mi archivo es de solo 1Mb. ¿Puede ayudarme a resolver esto (sé que podemos usar Table.Combine para lograrlo, pero eso no sirve para el propósito).

El siguiente código funciona bien si cargo datos de Excel, es decir, selecciono Excel Data.xlsx

let
    Source = Excel.Workbook(File.Contents(""E:ProjectTestFilesData.xlsx"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable 

Pero es demasiado lento en la última línea si cargo datos de la carpeta (en la barra de estado muestra la carga de más de 500 Mb de datos donde solo tengo 1 archivo en la carpeta con un tamaño de 1 Mb. También intenté usar Table.Buffer pero también va lento.

let
    Source = Folder.Files("E:ProjectTestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step5 ,{"Name"}),
    Step6  = Table.AddColumn(Step6, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    Step8 =Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    Step8

Hola @ImkeF

Tengo una carpeta con varios archivos .xls con la misma estructura, cambiará en el futuro. Estoy tratando de agregar todo como un solo archivo y no necesito todas las columnas. Tengo alrededor de 40 columnas y, según el negocio, solo necesitamos 20 columnas. Entonces me referí a su método de expandir la columna dinámicamente. El siguiente código funciona pero es demasiado lento.

let
    Source = Folder.Files("E:ProjectTestFiles"),
    Step1 = Table.SelectColumns(Source,{"Content"}),
    Step2 = Table.AddColumn(Step1, "Custom", each Excel.Workbook([Content])),
    Step3 = Table.SelectColumns(Step2, {"Custom"}),
    Step4 = Table.ExpandTableColumn(Step3, "Custom", {"Name", "Data"}, {"Name", "Data"}),
    Step5 = Table.RemoveColumns(Step4 ,{"Name"}),
    Step6  = Table.AddColumn(Step5, "PromoteHeader", each Table.PromoteHeaders([Data])),
    Step7 = Table.SelectColumns(Step6,{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(Step7,Table.ColumnNames(Step7){0}), each Table.ColumnNames(_))),
    ExpandTable = Table.ExpandTableColumn(Step7,"PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable

Para la prueba, solo tenía 1 archivo .xls de 1.5Mb en la carpeta, pero era demasiado lento y en la barra de estado mostraba que cargaba más de 500Mb (no tenía ni idea de cómo). Entonces, nuevamente intenté usar un solo archivo de Excel con el siguiente código y funcionó perfectamente bien.

let
    Source = Excel.Workbook(File.Contents("E:ProjectTestFilesData.xls"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.SelectColumns(#"Added Custom",{"PromoteHeader"}),
    DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))), 
    ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn)
in
    ExpandTable

Ahora mi pregunta es: 1. ¿Por qué es demasiado lento para el primer código? me estoy perdiendo cualquier cosa.

2. ¿Cómo se puede arreglar?

Espero que la explicación anterior te dé una idea de lo que estoy tratando de lograr.

Hola @ImkeF

Realmente lamento el error y no resalté el cambio en lugar de colocar el nuevo código y provocar la pérdida de su valioso tiempo. Primero publiqué con un sistema diferente, así que tuve que escribir el código manualmente al ver el código y es posible que lo pasé por alto en algunos puntos y, después de enviarlo, mostró un error y tuve que volver a escribir la publicación completa y causó un código incorrecto. Este es mi primer intento de publicar en el foro y lamento mucho mi error en el código y su tiempo también. Tendré mucho cuidado al publicar el código nuevamente.

Hola @ImkeF

Resolvió mi problema que usted por su orientación. 🙂 , ¿solo quiere entender qué hizo realmente este código? ¿Obligó a hacer el cálculo en la columna Distinct?