Transpose imported pdf table before joining it

[Syndicate_Admin]

Dear friends, good afternoon! I've been having a problem for several days and I can't find a solution.

I need to import a series of PDF files that have the following format through power query:

Each page has a series of characteristics that are repeated on each page, and throughout the different pages it shows this data for different people (name 1, 2, 3, 4, 5, etc.).

When importing the PDF power query creates a vertical table of 5 columns and X number of rows.

I need the import to be the other way around. That has 10 rows (one for each feature), and that has X columns (one for each name)

Here's what it should come up with:

Is it possible to do this from power query?

Thank you in advance! Best regards.

Christian

[Anonymous]

Hi @Syndicate_Admin ,

I create a PDF file and import it into the Power BI Desktop.

Then I create a new table and here is the DAX code.

Table =
VAR _vtable1 =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table001 (Page 1)',
            "Name1", 'Table001 (Page 1)'[Column1],
            "1", 'Table001 (Page 1)'[Column2],
            "2", 'Table001 (Page 1)'[Column3],
            "3", 'Table001 (Page 1)'[Column4]
        ),
        'Table001 (Page 1)'[Index] <= 4
    )
VAR _vtable2 =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table001 (Page 1)',
            "Name2", 'Table001 (Page 1)'[Column1],
            "4", 'Table001 (Page 1)'[Column2],
            "5", 'Table001 (Page 1)'[Column3],
            "6", 'Table001 (Page 1)'[Column4]
        ),
        'Table001 (Page 1)'[Index] <= 8
            && 'Table001 (Page 1)'[Index] > 4
    )
VAR _vtable3 =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table001 (Page 1)',
            "Name3", 'Table001 (Page 1)'[Column1],
            "7", 'Table001 (Page 1)'[Column2],
            "8", 'Table001 (Page 1)'[Column3],
            "9", 'Table001 (Page 1)'[Column4]
        ),
        'Table001 (Page 1)'[Index] <= 12
            && 'Table001 (Page 1)'[Index] > 8
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( _vtable1, _vtable2, _vtable3 ),
            [Name1] = [Name2]
                && [Name2] = [Name3]
        ),
        [Name1],
        [1],
        [2],
        [3],
        [4],
        [5],
        [6],
        [7],
        [8],
        [9]
    )

Then you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hello! Thanks for the reply!

The solution is good, but in my case I need to create tables from different PDFs, and several of them have different number of pages, between 5 and 25 each. I need something more automatic or generic. Can something like this be done from power query?

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

I create three tables as you mentioned.

Then I go to the Power Query and choose Merge Queries. Below are the steps and the M codes.

= Table.ExpandTableColumn(#"Merged Queries1", "T3", {"Number9", "Number10", "Number11", "Number12"}, {"T3.Number9", "T3.Number10", "T3.Number11", "T3.Number12"})

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.