Hi,
The attached file is an example of a file I get every week with data regarding damages to my various plots.
These files arrived as PDF, and I used PQ to convert it to Excel so I can translate the Hebrew.
The problem is that the plot number is not in any column, but as a sub-header (Cell C3 Plot number 6 in this example)
Goal:Read these files from a folder so I will have one file for all supervisions for all plots.
Problem: How to create a column that will contain "Plot 6" in all rows.
BTW, the problem can be solved with "Text to Columns", but I couldn't find a similar way in PQ.
Regards,
Ayal Telem.
Would the solution in the attached workbook work for you?
It assumes that the information with the plot, crop and area is always at the top of the 'Affiction' column.
@riny As always you provide 👍
The solution is exactly what I was looking for.
Send me the PQ code please, so I can implement it in the actual file.
Regards,
Ayal Telem.
Glad I could help. The code is actually in the workbook I uploaded earlier. you find it in the PQ editor. But just in case: this is the code I used to connect to the blue table and create the green one. Not very beautiful but it works.
let
Source = Excel.CurrentWorkbook(){[Name="Table001__Page_1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each #"Promoted Headers" [Affliction]{0}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Description] <> null))
in
#"Filtered Rows"
@riny I looked at the file in Excel online, which doesn't show the PQ menu.
I downloaded the file and looked at the PQ, and what I was missing was the knowledge how to create a new column that only contains the 1st cell of the "Affiction" column.
Thank you very much for that.
Ayal Telem.
@riny Hi,
I thought your solution will work because I thought that PQ will perform these steps for each file, but apparently it doesn't.
The AddCustomColumn step is performed only for the 1st file, so the value in C3 of the 1st file shows in all rows, although the data in the values in cell C3 are different between the files.
Any idea how to overcome this issue?
Regards,
Ayal Telem.
So you have the content of multiple PDF's in one table? Then you could add a column with some code that checks if the content of Column3 starts with "Plot". If so, use that value else enter null.
Then fill down and then split. See attached.
Alternatively, include the add column step from my first suggestion in the Transform function that is created in the "File, From folder" process. Then, invoking that function will open each PDF, add the column and then go to the next until all PDFs have been taken care of.
@riny SOLVED!
I ended up using Conditional Column to indentify the proper line, than used the Fill Down, Split, etc.
Thank you for your guidance.
Regards,
Ayal Telem.
