How do I transpose and merge 400 table extracted from a pdf

[dydx]

I have info about 400 companies with the same info for each company on each page

Like so

I am  trying to achieve the image below

I actually have 400 tables and 40 attributes. 

[v-kkf-msft · Accepted solution]

Hi @dydx ,

It is recommended that you place all data in a folder and then use the Folder connector to import the data. 

1. Select "Combine Files".

2. Then modify the code of the two tables: Transform Sample File and Excel Folder.

let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"unique company name", type text}, {"dfhh", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"attribute one", type text}, {"attribute 3", type text}, {"attribute 4", type text}})
in
    #"Changed Type1"

let
    Source = Folder.Files("\\xxxxx\xxxxxxxxxxx\Excel Folder"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "company name", each "Unique company name " & Text.Select([Name],{"0".."9"}))
in
    #"Added Custom"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with.