How to Group by in Power Query - Part 3: Power Query Challenge

It is time for Power Query Challenge Part Three made by Daniel Dion.

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#SUBSCRIBE #CURBAL

[GenePatNic]

Thank you for your good comments concerning my PQ query.

As already explained by Rolando/Luke:
#"Replaced Errors"[#"Column1 - Copy"]{[Index]}<>null: checked if the value of the column "Column1 - Copy" (the column that contains either a date or a null) for the next line
is not empty, the try/otherwise is necessary to avoid an error for the last row. Note: Power Query is based zero consequently the first line is line 0 not 1. After this step we
have a number coming from the index column only for the first line of each section.

I like your DAX Friday video...
Your tutorials are very good.

Daniel

[lukemccarthy]

His custom column labelled "Index" is a 1-based index column . Lists are zero-based. His reference to the list #"Replaced Errors"[#"Column1 - Copy"]{[Index]} is using the 1-based index value to reference a value in the zero-based list. So it will always reference the next row. This only works if there isn't a date value in the first row.

[fcoatis]

Hello Ruth,
I have no words to express the admiration I have for your initiative in bringing us such a high level degree of knowledge. I also extend my compliments to all the people who contributed in the comments especially to Daniel who shared this brilliant solution.

[excelisfun]

Thumbs Up on this Part3 Power Query Challenge!

[rolandotemporale]

Hola Ruth, buena solución al desafío!! el paso "AddCustom" utiliza la misma sentencia que tu utilizas en tu solución al promover rows a columnas pero aquí se utiliza para hallar los valores no nulos de la "Column1-Copy" y cargar el valor de "Index" a la nueva Columna agregada, (observar que "Index" comienza en "1" en este caso"), muy inteligente al realizar "Fill Down" para poder agrupar luego por este índice. Gracias por compartir!

[MrNillock]

WOW... that is one seriously awesome trick! much more elegant than my kludgy approach.

[nboisen]

Wow! That's quite fancy.

And thanks to Rolando for providing the reference to the Excel-Inside post about absolute & relative references. Very instructive.

So here's a question... Using this same logic, would there be a way to reference the "preceding" row (or any relative row) rather than the "next" row -- if needed in some other scenario -- by starting your index on a different value (or creating a new column that adds/subtracts the needed offset)?

[esrefatak]

You are talking too much but showing too little, so i am skipping continuously.