Cómo lidiar con columnas faltantes en Pivot

Tengo una consulta que pivota los datos y luego agrega algunas columnas de subtotales sumando los valores en algunas columnas.

es decir

Número de trabajo, tipo de costo A, tipo de costo B, tipo de costo C, tipo de costo 1, tipo de costo 2, suma A y B y C, suma 1 y 2, suma A y B y C y 1 y 2

Sin embargo, cuando uno de los tipos de costos falta en el pivote, ya que no tienen costos ese mes, la suma genera errores. ¿Alguien tiene una solución que me permita usar comodines para los nombres de las columnas en un foro, hacer que las columnas incluidas en una fórmula sean opcionales o agregar una columna si aún no está allí?

Actualmente, la única solución en la que puedo pensar es agregar la consulta a una consulta en blanco que ya tiene todos los nombres de las columnas …

Hola @ZLJ,

Puede consultar la siguiente fórmula para verificar las columnas existentes y agregar la columna que no existe:

let
    Source = Excel.Workbook(File.Contents("C:UsersxxxxxDesktoppoivt table with miss type.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JobName", type text}, {"Type", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum),
    #"Check Exist" =
let
    #"Check Column" =  if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column",
    #"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column",
    #"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1",
    #"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2",
    #"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3"
in
    #"Check Column4"
in
#"Check Exist"

tabla base:

Después de operar el pivote:

Después de la función «comprobar si existe»:

fórmula:

# «Comprobar existe» =
dejar
# «Check Column» = si no es Table.HasColumns (# «Pivoted Column», «Type A»), Table.AddColumn (# «Pivoted Column», «Type A», cada nulo) else # «Pivoted Column»,
# «Check Column1» = si no es Table.HasColumns (# «Check Column», «Type B») luego Table.AddColumn (# «Check Column», «Type B», cada nulo) else # «Check Column»,
# «Check Column2» = si no Table.HasColumns (# «Check Column1», «Type C») luego Table.AddColumn (# «Check Column1», «Type C», cada nulo) else # «Check Column1»,
# «Check Column3» = si no es Table.HasColumns (# «Check Column2», «Type 1») luego Table.AddColumn (# «Check Column2», «Type 1», cada nulo) else # «Check Column2»,
# «Check Column4» = si no es Table.HasColumns (# «Check Column3», «Type 2») luego Table.AddColumn (# «Check Column3», «Type 2», cada nulo) más # «Check Column3»
en
# «Marque la columna 4»

Saludos,
Xiaoxin Sheng

Hola @ZLJ,

Puede consultar la siguiente fórmula para verificar las columnas existentes y agregar la columna que no existe:

let
    Source = Excel.Workbook(File.Contents("C:UsersxxxxxDesktoppoivt table with miss type.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JobName", type text}, {"Type", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum),
    #"Check Exist" =
let
    #"Check Column" =  if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column",
    #"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column",
    #"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1",
    #"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2",
    #"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3"
in
    #"Check Column4"
in
#"Check Exist"

tabla base:

Después de operar el pivote:

Después de la función «comprobar si existe»:

fórmula:

# «Comprobar existe» =
dejar
# «Check Column» = si no es Table.HasColumns (# «Pivoted Column», «Type A»), Table.AddColumn (# «Pivoted Column», «Type A», cada nulo) else # «Pivoted Column»,
# «Check Column1» = si no es Table.HasColumns (# «Check Column», «Type B») luego Table.AddColumn (# «Check Column», «Type B», cada nulo) else # «Check Column»,
# «Check Column2» = si no Table.HasColumns (# «Check Column1», «Type C») luego Table.AddColumn (# «Check Column1», «Type C», cada nulo) else # «Check Column1»,
# «Check Column3» = si no es Table.HasColumns (# «Check Column2», «Type 1») luego Table.AddColumn (# «Check Column2», «Type 1», cada nulo) else # «Check Column2»,
# «Check Column4» = si no es Table.HasColumns (# «Check Column3», «Type 2») luego Table.AddColumn (# «Check Column3», «Type 2», cada nulo) más # «Check Column3»
en
# «Marque la columna 4»

Saludos,
Xiaoxin Sheng

Perfecto, gracias @ v-shex-msft. ¡Lo intentaré hoy!

Entradas relacionadas

Deja un comentario Cancelar respuesta