Forum Discussion

matheus_peppers's avatar
matheus_peppers
Frequent Visitor
3 years ago

Please help me format this table

Hello everyone, everything good?

I need help with something very important. I'm making a report of paid and unpaid slips, where I have 2 tables in common with the slip number and their value.

On my computer, I made two separate folders, where one contains only PDF documents with payment receipts, and in the other folder only PDF documents with the slips themselves.

What I want to do is the following:

1st I want to add up the value of the slips first, to see when the sum comes out. I thought of doing this through a DistintCount through DAX, where I will add the value of the slips through the different codes of each slip.

2nd Make the sum in the same scheme as above, but for the payment slips.

However, the biggest problem I'm having is in transforming the table so that I extract each of these values ​​from the PDF document. I even created an auxiliary table to help me visualize how I want to see the tables, and it looked like this:

 

Ticket Table:

 

Table of payment slips:

 

But I'm having a big problem that I have no idea how to solve. The table that comes to Power Query from the PDF's of payment receipts looks like this:

 

 

The table came very messy, where there are several lines that should be a header, and below the header value, for example:

 

BENEFICIARIO FINALCÓDIGO DO BOLETOPAGADOR:NR. DOCUMENTODATA DO PAGAMENTOVALOR DO DOCUMENTO
Facebook03399543492037736602487978001011390880000773500CA01 - Felipe Becari - 202281.80118/08/20227.735,00

 

Can anyone help me with any way to format this table this way?

 

So far I haven't been able to think of anything, and I still need to make this urgent delivery, due to the fact that it is an accountability of divergent values.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi matheus_peppers ,

     

    You may try to pivot it to make the row values to column headers.

    Select the designation column.

    Pivot (from the Transform menu)-> Choose value in the Values.

    Choose "Don't aggregate" from the Advanced

     

    Best Regards,

    Stephen Tao

     

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