Log In
Register
Home
November 27, 2020
Offline
Hi all
I loaded a data set having more than 14000 rows in power query to transform. In column 1, which contains unique account ID of more than 1000 rows and also contain certain blank rows in columns beyond 1000. In filter, this blanks is now showing. After "close & load" in excel sheet, I am able to find it. In power query, column 1, results of only 1000 rows showing with no option to load more results. Is there any way to clean data beyond 1000 rows? Pls help.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi,
Data view is limited in editor, will never load all data there. You have to scroll down untill you see the last row-1000, then the editor will load a few more rows.
You can apply any filters you want, they will be applied to all data, not just to visible editor data.
Even if you don't see a blank/null in dropdown, you can still filter them out. Filter out one of the visible items for example, then replace in formula bar the item with null:
= Table.SelectRows(#"Changed Type", each ([ColumnName] <> "Sample value"))
replace with:
= Table.SelectRows(#"Changed Type", each ([ColumnName] <> null))
November 27, 2020
Offline
Thank you Catalin. The scroll down of data irks. The ctrl+arrow not working in query editor. Have to use page down. Is there any shortcut keys to go to the last row of the data set in query editor.?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
PQ Editor is not excel, it has different functionality.
Load data to worksheet if you want to review all rows, PQ has only a data preview, in order to makes things faster. If you have 20 million rows of data, you will definitely want to have the opposite: to see less rows instead of waiting for all rows to load.
Add an index column then sort it from largest to smallest, you will see the last rows on top.
1 Guest(s)
