Awesome trick with that TextJoin. Just saved me a few hours of hand-cramping BS my dude so thank you!
@ExcelOlympics
Жыл бұрын
Very glad to hear that you found that helpfull!
@wayneedmondson1065
3 жыл бұрын
Hi Gasper. Great tip! Another cool option in Power Query is if you also have blank columns that you want to delete, you can first delete blank rows, then transpose, then delete blank rows again, then transpose again. The only problem with this is you lose the column names. If you want to keep the original column names, you can load the table, add an index column, unpivot other columns from index column, in this step.. Power Query automatically removes blank cells, then choose the Attribute column, pivot on Value / Don't Aggregate and then delete the index column.. close and load. This procedure will preserve the original column names.. less the blank columns (and rows). Power Query is full of surprises. Love learning new things about it. Thanks for sharing :)) Thumbs up!!
@ExcelOlympics
3 жыл бұрын
Oh yes, Power Query is a powerhouse. Now here's another tip for you. You can do the transpose and still retain column headers by doing the opposite of "use first row as headers" command. So effectively demoting the header row to first row before doing the transpose and then using the combine columns to get a single column where you filter out the blanks....
@wayneedmondson1065
3 жыл бұрын
@@ExcelOlympics Awesome! Just tried it.. works like a charm. Thanks for sharing that tip :)) Very handy!!
@ExcelOlympics
3 жыл бұрын
Don't mention it Wayne... I love to share Power Query tips as it means that people will use it more.
@IssueBoyStefan
3 жыл бұрын
Another handy one. I believe that instead of textjoin we can use also counta and filter the rows with zeros. However this will work only with true blanks (nulls) and not with empty strings. :) Thanks, Gasper!
@ExcelOlympics
3 жыл бұрын
You are right Stefan. That is one of the reasons I love Excel. There are usually multiple paths to a desired goal.
Пікірлер: 10