Realizar pasos de consulta en el archivo de Excel ...

Anonymous · ‎03-21-2020

Así que estoy importando un montón de archivos de Excel de SharePoint. Por alguna razón, la estructura de datos subyacente es un poco extraña. Cada archivo de Excel tiene encabezados de columna a partir de la fila 12. He confirmado esto entrando en cada archivo. Sin embargo, cuando se importan a Power BI, algunos archivos tienen encabezados de columna a partir de la fila 11 y otros de la fila 12. Estos archivos se generan a partir de un trabajo automatizado. Algunos archivos tienen el nombre de la hoja como la fecha en que se creó el archivo y algunos archivos solo tienen Sheet1 como fecha. Esto me hace pensar que algunos datos subyacentes están en formato csv si el nombre de la hoja tiene la fecha creada en lugar de Sheet1?

Esa es la raíz del problema.

Mi solución era desarrollar de alguna manera una manera en Power BI para examinar cada archivo de Excel entrante y ver si los encabezados de columna comienzan en la fila 11 y, a continuación, realizar los pasos de consulta A y si entran en la fila 12, a continuación, realizar los pasos de consulta B.

1.) Dupliqué el paso del archivo de muestra de transformación y lo convertí en una lista para identificar en qué fila están llegando los encabezados de columna y lo asigné como parámetro:

let
    Source = Excel.Workbook(Parameter2, null, true),
    FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
    #"Removed Top Rows" = Table.Skip(FirstSheet,10),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Top Rows", "Column1", "Column1 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",null,"",Replacer.ReplaceValue,{"Column1 - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Contains([#"Column1 - Copy"], "Shift") then [#"Column1 - Copy"] else "Adjust"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1 - Copy"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Down",{"Custom"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",1),
    Custom = #"Kept First Rows"[Custom]



in
    Custom

Parámetro:

"Shift" meta [IsParameterQuery=true, ExpressionIdentifier=#"Transform Sample File (3)", Type="Text", IsParameterQueryRequired=true]

2.) En el archivo de ejemplo de transformación original, realice la bifurcación condicional para realizar los pasos de consulta A o B en función de la configuración del parámetro anteriormente:

let
    Source = Excel.Workbook(Parameter2, null, true),
    FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
    #"Removed Top Rows" = Table.Skip(FirstSheet,10),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Top Rows", "Column1", "Column1 - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",null,"",Replacer.ReplaceValue,{"Column1 - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Contains([#"Column1 - Copy"], "Shift") then [#"Column1 - Copy"] else "Adjust"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1 - Copy"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}),
    //Adjust
    #"Removed Top Rows1000" = Table.Skip(#"Filled Down",1),
    #"Promoted Headers100" = Table.PromoteHeaders(#"Removed Top Rows1000", [PromoteAllScalars=true]),
    #"Changed Type100" = Table.TransformColumnTypes(#"Promoted Headers100",{{"Shift", Int64.Type}, {"Column2", type any}, {"EmpNbr", Int64.Type}, {"Name", type text}, {"Column5", type any}, {"Column6", type any}, {"Extn", type text}, {"Area", type text}, {"Location", type text}, {"Duration", Int64.Type}, {"Arvl", Int64.Type}, {"Lunch", Int64.Type}, {"Dptr", Int64.Type}, {"Total", Int64.Type}, {"Column15", type text}}),
    #"Removed Top Rows100" = Table.Skip(#"Changed Type100",2),
    #"Removed Columns100" = Table.RemoveColumns(#"Removed Top Rows100",{"Shift", "Column2", "Column5", "Column6", "Column15"}),
    #"Removed Blank Rows100" = Table.SelectRows(#"Removed Columns100", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Adjust Result" = #"Removed Blank Rows100",

    //Shift No adjust
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Shift", Int64.Type}, {"Column2", type any}, {"EmpNbr", Int64.Type}, {"Name", type text}, {"Column5", type any}, {"Column6", type any}, {"Extn", type text}, {"Area", type text}, {"Location", type text}, {"Duration", Int64.Type}, {"Arvl", Int64.Type}, {"Lunch", Int64.Type}, {"Dptr", Int64.Type}, {"Total", Int64.Type}, {"Column15", type text}}),
    #"Removed Top Rows1" = Table.Skip(#"Changed Type",2),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows1",{"Shift", "Column2", "Column5", "Column6", "Column15"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Shift Result" = #"Removed Blank Rows",

    result =
        if ManufacturingTeam= "Adjust"
        then #"Adjust Result"
        else #"Shift Result",
    #"Removed Columns2" = Table.RemoveColumns(result,{"Shift_1"})



in
    #"Removed Columns2"

Esto también se ha añadido a la función de transformar archivo:

let
    Source = (Parameter2 as binary, ManufacturingTeam as text) => let
        Source = Excel.Workbook(Parameter2, null, true),
        FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
        #"Removed Top Rows" = Table.Skip(FirstSheet,10),
        #"Duplicated Column" = Table.DuplicateColumn(#"Removed Top Rows", "Column1", "Column1 - Copy"),
        #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",null,"",Replacer.ReplaceValue,{"Column1 - Copy"}),
        #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.Contains([#"Column1 - Copy"], "Shift") then [#"Column1 - Copy"] else "Adjust"),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1 - Copy"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}),
        //Adjust
        #"Removed Top Rows1000" = Table.Skip(#"Filled Down",1),
        #"Promoted Headers100" = Table.PromoteHeaders(#"Removed Top Rows1000", [PromoteAllScalars=true]),
        #"Changed Type100" = Table.TransformColumnTypes(#"Promoted Headers100",{{"Shift", Int64.Type}, {"Column2", type any}, {"EmpNbr", Int64.Type}, {"Name", type text}, {"Column5", type any}, {"Column6", type any}, {"Extn", type text}, {"Area", type text}, {"Location", type text}, {"Duration", Int64.Type}, {"Arvl", Int64.Type}, {"Lunch", Int64.Type}, {"Dptr", Int64.Type}, {"Total", Int64.Type}, {"Column15", type text}}),
        #"Removed Top Rows100" = Table.Skip(#"Changed Type100",2),
        #"Removed Columns100" = Table.RemoveColumns(#"Removed Top Rows100",{"Shift", "Column2", "Column5", "Column6", "Column15"}),
        #"Removed Blank Rows100" = Table.SelectRows(#"Removed Columns100", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        #"Adjust Result" = #"Removed Blank Rows100",
    
        //Shift No adjust
        #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Shift", Int64.Type}, {"Column2", type any}, {"EmpNbr", Int64.Type}, {"Name", type text}, {"Column5", type any}, {"Column6", type any}, {"Extn", type text}, {"Area", type text}, {"Location", type text}, {"Duration", Int64.Type}, {"Arvl", Int64.Type}, {"Lunch", Int64.Type}, {"Dptr", Int64.Type}, {"Total", Int64.Type}, {"Column15", type text}}),
        #"Removed Top Rows1" = Table.Skip(#"Changed Type",2),
        #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows1",{"Shift", "Column2", "Column5", "Column6", "Column15"}),
        #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        #"Shift Result" = #"Removed Blank Rows",
    
        result =
            if ManufacturingTeam= "Adjust"
            then #"Adjust Result"
            else #"Shift Result",
        #"Removed Columns2" = Table.RemoveColumns(result,{"Shift_1"})
    
    
    
    in
        #"Removed Columns2"
in
    Source

Ahora, cuando dejo que esto se ejecute en mi consulta original, se rompe en la invocación de la función diciendo un error que 1 argumentos se pasaron a una función que espera 2.

Creo que tengo las bases para lo que necesito, pero creo que no estoy haciendo los pasos en el orden correcto que está causando el error de disparo.

¿Alguien tiene alguna idea?

Anonymous · ‎03-21-2020

Hola @nobodyukno , echa un vistazo a este artículo de blog de PowerPivotPro. Esto debería funcionar para ti.

La idea es encontrar el valor en una cierta "célula"... y considerar que el comienzo de la tabla. En su caso es A11 o A12. Tenga en cuenta que la solución de P3 solo funciona para filas de 16k. Esto se debe a que la solución "transpone la tabla" (intercambia columnas y filas) y Power BI solo puede admitir columnas de 16 k.

Escribí un comentario a mitad de camino que aborda el límite de fila de 16k: busque este texto para saltar a mi comentario:

Chris H

2 de agosto de 2019 a las 10:31 am

Aquí está el código M de mi comentario. esta es una función que puede aplicar a cada tabla para quitar el número correcto de columnas y filas.

let
Source =
,Starting_Value = “Label” // change as needed

// these first 3 steps are the same as Mike Ward’s solution
,Add_HeaderCheck_ROW = Table.AddColumn(Source, “Wanted”, each
List.Contains(Record.FieldValues(_), Starting_Value)),
Skip_RowsAboveHeader_ROW = Table.Skip(Add_HeaderCheck_ROW, each
[Wanted] true)
,Remove_HeaderCheck_ROW = Table.RemoveColumns(Skip_RowsAboveHeader_ROW
,{“Wanted”})

// keep only the first row to avoid transpose errors for large tables
,FirstRow_forColumns = Table.FirstN(Remove_HeaderCheck_ROW, 1)
,Transpose = Table.Transpose(FirstRow_forColumns)

// these 2 steps are the same as Mike Ward’s solution
,Add_HeaderCheck_COL = Table.AddColumn(Transpose, “Wanted”, each
List.Contains(Record.FieldValues(_), Starting_Value))
,Skip_RowsAboveHeader_COL = Table.Skip(Add_HeaderCheck_COL, each
[Wanted] true)

// count how many rows the previous step removed…this is how many columns to remove from the un-transposed table
,Rows_Removed = Table.RowCount(Add_HeaderCheck_COL) – Table.RowCount(Skip_RowsAboveHeader_COL)

// uses Datachant’s method of removing the first N columns from a table…without referencing column names
,Remove_NColumns = Table.RemoveColumns(Remove_HeaderCheck_ROW
,List.FirstN( Table.ColumnNames(Remove_HeaderCheck_ROW), Rows_Removed) )
,Headers = Table.PromoteHeaders(Remove_NColumns, [PromoteAllScalars=true])
in
Headers

También convertí este código M en una función a la que puede llamar para cada tabla. Si tuviera cada archivo como una fila independiente, podría hacer un Table.AddColumn() y crear la tabla "adecuada". Necesito desenterrar ese código. Avísame si lo necesitas.

Salud

Chris Haas

http://www.precisiondax.com

Realizar pasos de consulta en el archivo de Excel dinámicamente basado en parámetros dinámicos

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024