Forum Discussion
In Power Query aligning rows/columns
Eqa33 The PDF cleared up matters. The file contains 8 tables. The last three are inconsistent with the first 5. Some of the "header" rows have the 6xxx number combined with the [image] (see picture below)
This is really messing up the entire process, so I took these last three tables out. The other 5 tables neatly transform into the kind of table that you need. See if you can do something about the PDF files so that these odd cells like in the picture do not occur.
See attached. Just point the Source to the PDF on your own system.
When one column has nulls at the top followed by the data and the other columns have data at the top with nulls following after the data. If I filter by nulls in one column, I filter the other columns that have data in their rows.
Eqa33 You never included the query you used in any of the files so far. So, I can't see how you came from the "PDF data" to your table with all the nulls. But that's perhaps I'm lacking the imagination for it.
But, when you look at my query, it first transposes the table. Then splits the 2nd column and does some clean-up. That's it.
Riny_van_Eekelen Here is the Power Query. Please tell me what I did incorrectly, as you can see after having split the column and created 2 seperate columns I have data in one column where the other has nulls and visa-versa. Look forward to understanding what I did wrong.
Thanks,
Eqa
Eqa33 That file connects to a folder on your local PC, so I can't see what you are doing. I can "expand" the pivot table and see the underlying data but that creates a table like you had in your example.
From the transform function I see you connect to Table001 in the PDF document.
So please upload one PDF file or connect to the folder with the PDF files, perform the first steps that expands the tables and load that back to a table without any of the following transformations. Then upload that Excel file.