fill down in power query

%3CLINGO-SUB%20id%3D%22lingo-sub-958999%22%20slang%3D%22en-US%22%3Efill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958999%22%20slang%3D%22en-US%22%3E%3CP%3Ewhy%20power%20query%20is%20not%20responding%20to%20fill%20donw%20option%3F%20i%20want%20fill%20down%20in%20colunmn%20named%20%22name%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlz%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-958999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-959050%22%20slang%3D%22en-US%22%3ERe%3A%20fill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-959050%22%20slang%3D%22en-US%22%3EI%20expect%20it%20is%20because%20the%20rows%20that%20appear%20to%20be%20blank%20in%20reality%20are%20not%20blank%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322453%22%20slang%3D%22en-US%22%3ERe%3A%20fill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%20Jan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20mean%20that%20cells%20not%20%22null%22%20are%20not%20empty%20even%20they%20have%20no%20characters%20seen%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20could%20i%20erase%20the%20%22invisible%20content%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Juan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324098%22%20slang%3D%22en-US%22%3ERe%3A%20fill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324098%22%20slang%3D%22en-US%22%3EYou%20could%20add%20a%20column%20which%20takes%20the%20length%20of%20the%20string%20in%20the%20column%20you%20are%20interested%20in%20and%20filter%20the%20query%20on%20non-zero%20length%20strings.%20Otherwise%2C%20check%20out%20the%20right-click%20menu%20when%20you%20right-click%20a%20column.%20Especially%20the%20Transform%20sub-menu%20gives%20some%20cleaning%20options.%20Finally%20you%20could%20do%20a%20search%20and%20replace%20for%20certain%20non-printing%20characters.%20Infamous%20characters%20causing%20problems%20are%20ASCII%20160%20(non-breaking%20space)%20ASCII%2010%2C%2012%20and%2013%20(line%20feed%2C%20carriage%20return%2Blinefeed%2C%20carriage%20return).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324296%22%20slang%3D%22en-US%22%3ERe%3A%20fill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%20unfortunately%20I%20was%20not%20able%20to%20fill%20down.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20look%20the%20attached%20file%20and%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324689%22%20slang%3D%22en-US%22%3ERe%3A%20fill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324689%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFilling%20down%20only%20works%20if%20the%20value%20is%20null.%26nbsp%3B%20You'll%20need%20to%20select%20the%20name%20column%2C%20replace%20the%20blank%20(%22%22)%20values%20with%20%22null%22%20(I%20couldn't%20use%20your%20example%20because%20the%20source%20data%20wasn't%20provided).%26nbsp%3B%20Then%20fill%20down.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324903%22%20slang%3D%22en-US%22%3ERe%3A%20fill%20down%20in%20power%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324903%22%20slang%3D%22en-US%22%3Eit%20works!!%20thank%20you%20very%20much!!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

why power query is not responding to fill donw option? i want fill down in colunmn named "name".

 

Plz help me.

 

6 Replies
Highlighted
I expect it is because the rows that appear to be blank in reality are not blank?
Highlighted

@Jan Karel Pieterse 

 

hi Jan,

 

Does that mean that cells not "null" are not empty even they have no characters seen?

 

How could i erase the "invisible content"?

 

Thanks, Juan

Highlighted
You could add a column which takes the length of the string in the column you are interested in and filter the query on non-zero length strings. Otherwise, check out the right-click menu when you right-click a column. Especially the Transform sub-menu gives some cleaning options. Finally you could do a search and replace for certain non-printing characters. Infamous characters causing problems are ASCII 160 (non-breaking space) ASCII 10, 12 and 13 (line feed, carriage return+linefeed, carriage return).
Highlighted

@Jan Karel Pieterse 

 

Thank you for your help. unfortunately I was not able to fill down. 

 

Can you look the attached file and help me?

 

 

Highlighted

@juan jimenez 

Filling down only works if the value is null.  You'll need to select the name column, replace the blank ("") values with "null" (I couldn't use your example because the source data wasn't provided).  Then fill down.  

Highlighted
it works!! thank you very much!!