Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Transpose imported pdf table before joining it

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's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.