Counting columns

[Schumacher]

Hi

I'm about to load in a PDF to power query. The PDF is a long sales list and Power Query don't regonize that it's a big table, which means that every page has its own table. It's only the first page which has headers.

In some instances Power Query thinks there is 6 columns and in other only 5 columns. I should merge column 2, 3 and 4 when there i 6 columns and merge column 2 and 3 when there is 5 columns. Is there anyway you can count the total number of columns for each table and then put an if statement to merge the columns?

Best Regards

M. Schumacher

[Anonymous]

Hi @Schumacher ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[BA_Pete]

Hi @Schumacher ,

PDF to Power Query. Yuck 🤢

The basic structure would be something like this but, without seeing an example of your 5/6 column tables, it may not be perfect for your situation;

let
  Source = aTable,
  mergeCols =
    if Table.ColumnCount(Source) = 5
    then Table.CombineColumns(
      Source,
      {"Column2", "Column3"},
      Combiner.CombineTextByDelimiter("", QuoteStyle.None),
      "Merged"
    )
    else if Table.ColumnCount(Source) = 6
    then Table.CombineColumns(
      Source,
      {"Column2", "Column3", "Column4"},
      Combiner.CombineTextByDelimiter("", QuoteStyle.None),
      "Merged"
    )
    else Source,
in
  mergeCols

Pete