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: ...
  • 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.

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.