December 13, 2021
Attached excel file contains monthly payroll data in an excel table in worksheet "May'2022".
I attempt to use Power Query to transform the data into a table suitable for analysis "Table1 (2).
One of the transformation process was to replace text as blank. However, the cell value contain an single open quotation mark '.
As a result, the fill process was unsuccessful.
Can anyone suggest a work around or alternative process.
Appreciate greatly.
October 5, 2010
Hi Johnson,
In the query I don't see any step that tries to Fill Down. I also don't see where you tried to replace ' as blank. And if I search the table on the May 2022 sheet I only find 2 cells with a ' in them. So I'm a bit confused as I can't see the data or query steps you describe in the problem.
Regards
Phil
Moderators
January 31, 2022
Hi,
I note that you mimicked a Fill operation by manually entering formulae in column E (Projects) in the Table 1 (2) sheet.
When you say that the "Fill process was unsuccessful", that's because the cells you want the fill in the Project column (in PQ) are blank. They should contain null in order to apply Fill Down. So, when you alter all the 'Replaced Value' steps to look like in the picture below it will work as desired. File attached with the changes included.
1 Guest(s)