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"
