Looping through multiple PDFs. One makes Problems.

[moritz0815]

Hello dear community,

I am new to using Power Query and unfortunately I have encountered a problem that is unsolvable for me right now.

My scenario: I have about 15 PDF files, each containing a number of pages. I want to read all pages of all PDF files via PowerQuery and write the data into an excel file.

So far what I have learned now works fine.

Unfortunately, two pages of a single PDF are causing me big problems. All pages of the PDFs have 22 columns. I have to partially merge and rename them. One page has only 18 columns. However, I was able to catch this using the try ... otherwise statement. Another page has only 11 columns and a slightly different structure. This one causes big problems for me. Visually this page looks the same as all the others.

When updating the data, the query of course stops at the said PDF and I get the message that a column was not found.

Do you have any idea how I can solve this problem.

Short explanation about PDF-Pfad and Seite:
In a separate table the paths of the pdf (PDF-Pfad) file and the number of pages (Seite) are read and passed.

Here is my code (sorry the names of the steps are german):

let Schleife=(PDF_Pfad, Seite) =>

let
    Quelle = Pdf.Tables(File.Contents(PDF_Pfad), [Implementation="1.3"]),
    Page1 = Quelle{[Id=Seite]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
    #"Geänderter Typ" = try Table.TransformColumnTypes(#"Höher gestufte Header",{{"Bericht", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}}) otherwise Table.TransformColumnTypes(#"Höher gestufte Header",{{"Bericht Leistungsstand für Fachhochschulreife", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", type text}, {"Column9", Int64.Type}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}}),
    #"Zusammengeführte Spalten" = Table.CombineColumns(#"Geänderter Typ",{"Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"),
    #"Zusammengeführte Spalten1" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten", {{"Column6", type text}}, "de-DE"),{"Column5", "Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/2"),
    #"Zusammengeführte Spalten2" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten1", {{"Column9", type text}}, "de-DE"),{"Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"),
    #"Zusammengeführte Spalten3" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten2", {{"Column12", type text}}, "de-DE"),{"Column11", "Column12", "Column13"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/2"),
    #"Zusammengeführte Spalten4" = Table.CombineColumns(#"Zusammengeführte Spalten3",{"Column14", "Column15", "Column16"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Punkte AP"),
    #"Zusammengeführte Spalten5" = try Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten4", {{"Column18", type text}}, "de-DE"),{"Column19", "Column18", "Column17"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Gesamtergebnis") otherwise #"Zusammengeführte Spalten4",
    #"Zusammengeführte Spalten6" = try Table.CombineColumns(#"Zusammengeführte Spalten5",{"Column20", "Column21", "Column22"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Note") otherwise #"Zusammengeführte Spalten4",
    #"Umbenannte Spalten" = Table.RenameColumns(#"Zusammengeführte Spalten6",{{"Bericht", "Infos und Fächer"}}),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Umbenannte Spalten",5)

in
    #"Entfernte untere Zeilen"

in Schleife

[KT_Bsmart2gethe · Accepted solution]

Hi @moritz0815 ,

Let's start from scratch by modifying your existing code. We need to create to custom functions, 22 columns and 11 columns.

Once we have the two functions, follow the steps below:

1. 

2.

3. Filter for page only

4. Add a custom column

5. Combine tables by right-click on the column header and select drill down.

6. Update the formula in the formula bar by adding Table.Combine(defaultCode[columnName]) 

Please see below code for the two custom functions:

Copy and paste each of them to blank query and the queries to fx22Columns and fx11Columns.

//fx22Columns
(FilePath as text, PageID as text)=>
let
Quelle = Pdf.Tables(File.Contents(FilePath), [Implementation="1.3"]),
Page1 = Quelle{[Id=PageID]}[Data],
#"Höher gestufte Header" = Table.PromoteHeaders(
Page1, [PromoteAllScalars=true]),
#"Geänderter Typ" = Table.TransformColumnTypes(
#"Höher gestufte Header",
List.Transform(
Table.ColumnNames(#"Höher gestufte Header"
),
each {_, type text}
)
),
#"Zusammengeführte Spalten22_1" = Table.CombineColumns(
#"Geänderter Typ",
{"Column2", "Column3", "Column4"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"
),
#"Zusammengeführte Spalten22_2" = Table.CombineColumns(
#"Zusammengeführte Spalten22_1",
{"Column5", "Column6", "Column7"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/2"
),
#"Zusammengeführte Spalten22_3" = Table.CombineColumns(
#"Zusammengeführte Spalten22_2",
{"Column8", "Column9", "Column10"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"
),
#"Zusammengeführte Spalten22_4" = Table.CombineColumns(
#"Zusammengeführte Spalten22_3",
{"Column11", "Column12", "Column13"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/2"
),
#"Zusammengeführte Spalten22_5" = Table.CombineColumns(
#"Zusammengeführte Spalten22_4",
{"Column14", "Column15", "Column16"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Punkte-AP"
),
#"Zusammengeführte Spalten22_6" = Table.CombineColumns(
#"Zusammengeführte Spalten22_5",
{"Column17", "Column18", "Column19"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Gesamtergebnis"
),
#"Zusammengeführte Spalten22_7" = Table.CombineColumns(
#"Zusammengeführte Spalten22_6",
{"Column20", "Column21", "Column22"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Note"
),
#"Entfernte untere Zeilen" = Table.RemoveLastN(
#"Zusammengeführte Spalten22_7",
5
),
#"Umbenannte Spalten" = Table.RenameColumns(
#"Entfernte untere Zeilen",
{
{"Bericht Leistungsstand für Fachhochschulreife",
"Fächer und Infos"}
}
)
in
#"Zusammengeführte Spalten22_7"

//fx11Columns
(PageID as text)=>
let
Quelle = Pdf.Tables(File.Contents("C:\Speicher ohne Upload\F12Ib.pdf"), [Implementation="1.3"]),
Page1 = Quelle{[Id="Page010"]}[Data],
#"Höher gestufte Header" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
#"Geänderter Typ" = Table.TransformColumnTypes(
#"Höher gestufte Header",
List.Transform(
Table.ColumnNames(#"Höher gestufte Header"
),
each {_, type text}
)
),
#"Zusammengeführte Spalten" = Table.CombineColumns(
#"Geänderter Typ",
{"Column1", "Bericht Leistungsstand für Fachhochschulreife"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Bericht Leistungsstand für Hochschulreife"
),
#"Zusammengeführte Spalten1" = Table.CombineColumns(
#"Zusammengeführte Spalten",
{"Column3", "Column4"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"
),
#"Umbenannte Spalten" = Table.RenameColumns(
#"Zusammengeführte Spalten1",
{
{"Column5", "11/2"}
}
),
#"Zusammengeführte Spalten2" = Table.CombineColumns(
#"Umbenannte Spalten",
{"Column6", "Column7"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"
),
#"Umbenannte Spalten1" = Table.RenameColumns(
#"Zusammengeführte Spalten2",
{
{"Column8", "12/2"}, {"Column9", "Punkte-AP"}, {"Column10", "Gesamtergebnis"}, {"Column11", "Note"}
}
)
in
#"Umbenannte Spalten1"

Let me know how you go.

Regards

KT

[moritz0815]

Wow! Great Tor habe you!

I will go step by step tomorrow. I will let you know.

[moritz0815]

@KT_Bsmart2gethe 

Thanks a million for your effort and the extremely helpful tutorial and good tips.
I had to make a few adjustments and got it working. I have also learned a lot.

[moritz0815]

Thank you so much. 

I tried it like this: 

let Schleife=(PDF_Pfad, Seite) =>

let
    Quelle = Pdf.Tables(File.Contents(PDF_Pfad), [Implementation="1.3"]),
    Page1 = Quelle{[Id=Seite]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_,text})),
    query0 = if List.Count(Table.ColumnNames())=22 then query1 else #"Zusammengeführte Spalten" = Table.CombineColumns(#"Geänderter Typ",{"Column1", "Bericht Leistungsstand für Fachhochschulreife"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Bericht Leistungsstand für Fachhochschulreife.1"),
    query1 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten" = Table.CombineColumns(#"Geänderter Typ",{"Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1") else #"Zusammengeführte Spalten1" = Table.CombineColumns(#"Zusammengeführte Spalten",{"Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"),
    query2 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten1" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten", {{"Column6", type text}}, "de-DE"),{"Column5", "Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/2") else #"Umbenannte Spalten" = Table.RenameColumns(#"Zusammengeführte Spalten1",{{"Column5", "11/2"}}),
    query3 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten2" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten1", {{"Column9", type text}}, "de-DE"),{"Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1") else #"Zusammengeführte Spalten2" = Table.CombineColumns(Table.TransformColumnTypes(#"Umbenannte Spalten", {{"Column7", type text}}, "de-DE"),{"Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"),
    query4 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten3" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten2", {{"Column12", type text}}, "de-DE"),{"Column11", "Column12", "Column13"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/2") else #"Umbenannte Spalten1" = Table.RenameColumns(#"Zusammengeführte Spalten2",{{"Column8", "12/2"}, {"Column9", "Punkte-AP"}, {"Column10", "Gesamtergebnis"}, {"Column11", "Note"}}),
    query5 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten4" = Table.CombineColumns(#"Zusammengeführte Spalten3",{"Column14", "Column15", "Column16"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Punkte AP") else #"Umbenannte Spalten",
    query6 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten5" = Table.CombineColumns(Table.TransformColumnTypes(#"Zusammengeführte Spalten4", {{"Column18", type text}}, "de-DE"),{"Column19", "Column18", "Column17"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Gesamtergebnis") else #"Umbenannte Spalten",
    query7 = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten6" = Table.CombineColumns(#"Zusammengeführte Spalten5",{"Column20", "Column21", "Column22"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Note") else #"Umbenannte Spalten",
    #"Umbenannte Spalten" = Table.RenameColumns(#"Zusammengeführte Spalten6",{{"Bericht Leistungsstand für Fachhochschulreife", "Infos und Fächer"}}),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Umbenannte Spalten",5)

in
    #"Entfernte untere Zeilen"

in Schleife

But I get: Expression error: The name 'text' wasn't recognized. Make sure it's spelled correctly

[KT_Bsmart2gethe]

Sorry, it was my bad.

i missed type (see below highlighted in blue)

List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_, type text})),

Regards

KT

[moritz0815]

Unfortunately, I can't get it to work.
I don't know how to bring the two if blocks back together into one.

My attempt fails unfortunately. Expression error: The name 'Umbenannte Spalten2' wasn't recognized. Make sure it's spelled correctly

let Schleife=(PDF_Pfad, Seite) =>

let
    Quelle = Pdf.Tables(File.Contents(PDF_Pfad), [Implementation="1.3"]),
    Page1 = Quelle{[Id=Seite]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_, type text})),
    #"if1" = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten22_1" else #"Zusammengeführte Spalten",
    #"Zusammengeführte Spalten22_1" = Table.CombineColumns(#"Geänderter Typ",{"Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"),
    #"Zusammengeführte Spalten22_2" = Table.CombineColumns(#"Zusammengeführte Spalten22_1",{"Column5", "Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/2"),
    #"Zusammengeführte Spalten22_3" = Table.CombineColumns(#"Zusammengeführte Spalten22_2",{"Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"),
    #"Zusammengeführte Spalten22_4" = Table.CombineColumns(#"Zusammengeführte Spalten22_3",{"Column11", "Column12", "Column13"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/2"),
    #"Zusammengeführte Spalten22_5" = Table.CombineColumns(#"Zusammengeführte Spalten22_4",{"Column14", "Column15", "Column16"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Punkte-AP"),
    #"Zusammengeführte Spalten22_6" = Table.CombineColumns(#"Zusammengeführte Spalten22_5",{"Column17", "Column18", "Column19"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Gesamtergebnis"),
    #"Zusammengeführte Spalten22_7" = Table.CombineColumns(#"Zusammengeführte Spalten22_6",{"Column20", "Column21", "Column22"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Note"),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Zusammengeführte Spalten22_7",5),
    #"if2" = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten1" else #"Zusammengeführte Spalten",
    #"Zusammengeführte Spalten" = Table.CombineColumns(#"Geänderter Typ",{"Column1", "Bericht Leistungsstand für Fachhochschulreife"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Bericht Leistungsstand für Hochschulreife"),
    #"Zusammengeführte Spalten1" = Table.CombineColumns(#"Zusammengeführte Spalten",{"Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Zusammengeführte Spalten1",{{"Column5", "11/2"}}),
    #"Zusammengeführte Spalten2" = Table.CombineColumns(#"Umbenannte Spalten",{"Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Zusammengeführte Spalten2",{{"Column8", "12/2"}, {"Column9", "Punkte-AP"}, {"Column10", "Gesamtergebnis"}, {"Column11", "Note"}}),
    #"Entfernte untere Zeilen2" = Table.RemoveLastN(#"Umbenannte Spalten1",5),
    #"if3" = if List.Count(Table.ColumnNames())=22 then #"Umbenannte Spalten2" = Table.RenameColumns(#"Entfernte untere Zeilen",{{"Bericht Leistungsstand für Fachhochschulreife", "Fächer und Infos"}}) else #"Umbenannte Spalten3" = Table.RenameColumns(#"Entfernte untere Zeilen2",{{"Bericht Leistungsstand für Fachhochschulreife", "Fächer und Infos"}})
in
    #"if3"

in Schleife

If the PDF has 22 columns the following should be done:

let
    Quelle = Pdf.Tables(File.Contents("C:\Speicher ohne Upload\F12Ib.pdf"), [Implementation="1.3"]),
    Page1 = Quelle{[Id="Page001"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_, type text})),
    #"Zusammengeführte Spalten22_1" = Table.CombineColumns(#"Geänderter Typ",{"Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"),
    #"Zusammengeführte Spalten22_2" = Table.CombineColumns(#"Zusammengeführte Spalten22_1",{"Column5", "Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/2"),
    #"Zusammengeführte Spalten22_3" = Table.CombineColumns(#"Zusammengeführte Spalten22_2",{"Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"),
    #"Zusammengeführte Spalten22_4" = Table.CombineColumns(#"Zusammengeführte Spalten22_3",{"Column11", "Column12", "Column13"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/2"),
    #"Zusammengeführte Spalten22_5" = Table.CombineColumns(#"Zusammengeführte Spalten22_4",{"Column14", "Column15", "Column16"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Punkte-AP"),
    #"Zusammengeführte Spalten22_6" = Table.CombineColumns(#"Zusammengeführte Spalten22_5",{"Column17", "Column18", "Column19"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Gesamtergebnis"),
    #"Zusammengeführte Spalten22_7" = Table.CombineColumns(#"Zusammengeführte Spalten22_6",{"Column20", "Column21", "Column22"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Note"),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Zusammengeführte Spalten22_7",5),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte untere Zeilen",{{"Bericht Leistungsstand für Fachhochschulreife", "Fächer und Infos"}})
in
    #"Zusammengeführte Spalten22_7"

And if there are 11 columns: 

let
    Quelle = Pdf.Tables(File.Contents("C:\Speicher ohne Upload\F12Ib.pdf"), [Implementation="1.3"]),
    Page1 = Quelle{[Id="Page010"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_, type text})),
    #"Zusammengeführte Spalten" = Table.CombineColumns(#"Geänderter Typ",{"Column1", "Bericht Leistungsstand für Fachhochschulreife"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Bericht Leistungsstand für Hochschulreife"),
    #"Zusammengeführte Spalten1" = Table.CombineColumns(#"Zusammengeführte Spalten",{"Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"11/1"),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Zusammengeführte Spalten1",{{"Column5", "11/2"}}),
    #"Zusammengeführte Spalten2" = Table.CombineColumns(#"Umbenannte Spalten",{"Column6", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"12/1"),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Zusammengeführte Spalten2",{{"Column8", "12/2"}, {"Column9", "Punkte-AP"}, {"Column10", "Gesamtergebnis"}, {"Column11", "Note"}})
in
    #"Umbenannte Spalten1"

Where the last two lines of the block with 22 columns, should then also be applied to a PDF with 11 columns. So the renaming and deleting of the last 5 lines.

[KT_Bsmart2gethe]

let me go through your code line by line and get back to you.

[moritz0815]

Thank your so much!

The Line 

    #"if2" = if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte Spalten1" else #"Zusammengeführte Spalten",

must be

    #"if2" = if List.Count(Table.ColumnNames())=22 then #"if3" else #"Zusammengeführte Spalten",

But the error stays the same. I think the error comes with the last line. But I don't know hat to End the Query. 

[KT_Bsmart2gethe]

Hi @moritz0815 ,

Without looking at sample data, it is a challenge to provide the solution; however, based on your explanation and your code. Here are a few things you can try:

1. you can simply the code for step (based on your code. you defined all columns as text, and below code will dynamically define all columns as text):

#"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_,text})),

2. 22 columns is the standard and to deal with abnormalities, you can apply:

if List.Count(Table.ColumnNames())=22 then #"Zusammengeführte SpaltenX" else #"Zusammengeführte SpaltenY"

I hope it helps.

Regards

KT