The column of the table was not found

[bashairmu]

I’m working on extracting columns from multiple PDFs using Power Query (Combine and Transform). However, one of the columns is missing from some of the PDFs, which causes the loading process to fail. Is there a function that can enforce a null value when a column doesn’t exist?

[Anonymous · Accepted solution]

Hi, @bashairmu 

I am glad to help you.

Perhaps you can refer to my M code:

let
    PDF1 = Table.FromRecords({
        [Company Name="ABC Corp", Company ID=12345, Revenue=100000],
        [Company Name="XYZ Ltd", Company ID=67890, Revenue=150000]
    }),
    PDF2 = Table.FromRecords({
        [Company Name="DEF Inc", Revenue=200000],
        [Company Name="GHI LLC", Revenue=250000]
    }),
    PDF3 = Table.FromRecords({
        [Company Name="JKL Co", Company ID=54321, Revenue=300000],
        [Company Name="MNO Group", Company ID=98765, Revenue=350000]
    }),
    CombinedPDFs = Table.Combine({PDF1, PDF2, PDF3}),

    AddMissingCompanyIDColumn = (inputTable as table) as table =>
    let
        AddMissingColumn = if Table.HasColumns(inputTable, "Company ID") then
            inputTable
        else
            Table.AddColumn(inputTable, "Company ID", each null)
    in
        AddMissingColumn,

    Result = AddMissingCompanyIDColumn(CombinedPDFs)

in
    Result

Result:

I have attached the pbix file for this example below, I hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AsNa_92]

Hi

You can edit your query by adding a new column and refresh again from advance editor, example:

let
    Source = Excel.Workbook(File.Contents("Y:\Data.xls"), null, true),
    #"Promoted Headers" = Table.PromoteHeaders(#"Detailed Report1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", Int64.Type}, {new column}})
in
    #"Changed Type"

[bashairmu]

Could you elaborate further? Knowing that I have over 100 PDFs, and some of them do not include a column named "Company ID." What I need is to check the PDF: if the "Company ID" column exists, I want to extract its value; if it doesn't exist, I would like to assign a null value.