How to cope with missing columns in Pivot

Hi ZLJ,

You can refer to below formula to check exist columns and add the column not exist:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\poivt 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"

base table:

After pivot operate:

After "check exist" function:

formula:

    #"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"

Regards,
Xiaoxin Sheng