PowerQuery question: what's the best way to fix misaligned columns?

[ExcelOnTheClock]

Hello,

I'm trying to import several PDF files into PowerQuery. There's a section in the PDF that looks like this:

However, when I import the PDFs, the columns keep getting misaligned, looking like this jumbled mess:

What's the best way to fix it?

Thanks in advance for the help!

 

[ExcelOnTheClock]

Also Amy's row has a mistake. There's four 1s in her row, I shouldn't have included those. It should just be 2 items per person.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.AddColumn(Source, "Custom", each List.RemoveNulls(Record.ToList(_))),
    Result = Table.FromRows(tbl[Custom], {"Person","Item1", "Item2"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Person	Item1	Item2	Item3	Item4	Item5	Item6		Person	Item1	Item2
2	A	1	1						A	1	1
3	B		1	1					B	1	1
4	C				1	1			C	1	1
5	D					1	1		D	1	1
6	E	1	1						E	1	1
7	F		1	1					F	1	1
8	G					1	1		G	1	1
9
Sheet2

 

[ExcelOnTheClock]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.AddColumn(Source, "Custom", each List.RemoveNulls(Record.ToList(_))),
    Result = Table.FromRows(tbl[Custom], {"Person","Item1", "Item2"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Person	Item1	Item2	Item3	Item4	Item5	Item6		Person	Item1	Item2
2	A	1	1						A	1	1
3	B		1	1					B	1	1
4	C				1	1			C	1	1
5	D					1	1		D	1	1
6	E	1	1						E	1	1
7	F		1	1					F	1	1
8	G					1	1		G	1	1
9
Sheet2

Thanks! The easiest way to do this is to write this M code? I'm VERY new to PowerQuery, was hoping there was a less custom way. If not, all good! This will get the job done!

 

[GraH]

Hello,

I'm trying to import several PDF files into PowerQuery. There's a section in the PDF that looks like this:
View attachment 80488

However, when I import the PDFs, the columns keep getting misaligned, looking like this jumbled mess:
View attachment 80489

What's the best way to fix it?

Thanks in advance for the help!

If you want a UI solution... Do as you do, finally select the first column and select Unpivot other columns from the ribbon.
Should do the trick.

 

[GraH]

Hi ExcelOnTheClock,

I saw this as an opportunity to learn "import from pdf", as I never needed it before. So I tried to simulate the issue.
Assuming all PD files are alike, I did put them together in a single folder.
PDFs look like below, a simple mock-up.

Loading from folder and applying the default transformation function it creates, the result ends up nicely.

let
    Source = (Parameter1) => let
        Source = Pdf.Tables(Parameter1, [Implementation="1.3"]),
        Table001 = Source{[Id="Table001"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Table001, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    Source

let
    Source = Folder.Files("G:\Uploads\PDF_mrXlForum"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Person", type text}, {"Item", Int64.Type}, {"Item2", Int64.Type}})
in
    #"Changed Type"

So I now wonder what's been your way to get to the messed-up table? But maybe the above is a way to avoid custom M-code and have a UI based solution. Not my previous reply, as I misunderstood you actually want 2 columns for each row.