Dynamic Coalesce in Power Query ( Record.SelectFields() )

Рет қаралды 2,990

Күн бұрын

In this video we show you how to perform coalesce on a dynamic selection of columns using Record.SelectFields() function, an extremely important one when dealing with record type M objects!
👉We also wrote a blog on this topic that you can find in the following link: exceed.hr/blog/dynamic-coales...
👉To learn more about Power Query, check out our Power Query fundamentals course: exceed.hr/en/academy/powerque...
Working file is available here: exceed.hr/wp-content/uploads/...
#powerquery #powerbi #etltools #data #dataanalysis

Пікірлер: 20

@datawizibility 6 күн бұрын

Awesome....great way of teaching... making complex things so easy ...

@vacilando86 2 жыл бұрын

Awesome

@engahmedelbendary1566 2 жыл бұрын

thx alot for your Effort and go on

@umangdbz 2 жыл бұрын

Awesome bro !

@rick_. 2 жыл бұрын

I always learn something from your videos. BTW, you could also have used List.Skip(,2) to remove the first two columns, either for filtering the column names or when selecting the record values. = Table.AddColumn(Source, "Coalesce",each List.Last(List.RemoveNulls( List.Skip(Record.ToList(_),2))))

@ExceedLearning 2 жыл бұрын

Glad you find our videos helpful! :) Yes, for the first part of the video we could have used List.Skip, as it would return the same result. However, using Record.SelectFields is a more robust solution. When we moved the column Type (at around 8:00), the solution with List.Skip would remove the column Jan instead of Type, and it would return the wrong result.

@smvpegb 2 ай бұрын

thankyou for this video is very helpful

@malchicken 2 жыл бұрын

Best power query vids. Unfortunately, I don’t really see your videos shown under related videos when I watch power query videos. To consider, maybe adding hashtags to the description or related channels to your channel page will help the KZfaq algorithm show your videos for related videos.

@norbertschmidt6532 2 жыл бұрын

Dear EL, thanks, great and super.

@dabizness6662 9 ай бұрын

So thats what _ means ! Thank yiu fir explaining. I ve been doing PQ for 1 year didn’t know.

@silverfunnel6819 2 жыл бұрын

Very useful. Thank you!

@silverfunnel6819 2 жыл бұрын

It would be interesting to have the corresponding column name in a new column🙂

@SamehRSameh 2 жыл бұрын

We Need more about M language and power query

@MrWella1234 2 жыл бұрын

Thanks for the tutorial. What about if you use the "Record.RemoveFields" insted of "Record.SelectFields"? then you can remove the name and type column and then it will be dynamic.

@ExceedLearning 2 жыл бұрын

Hi, It is true that by using Record.RemoveFields you can achieve the same solution (similar to remove columns or remove other columns in the PQ user interface). We used Record.SelectFields because sometimes there are more complex filter conditions to evaluate, and that's where the Record.SelectFields can be a better choice.

@marcosflatron 2 жыл бұрын

Volim te 🥰

@epernayjfl 2 жыл бұрын

Bonjour et merci pour ce partage de connaissances ! Poster votre fichier exemple serait bénéfique pour nous ! 😉

@ExceedLearning 2 жыл бұрын

¡Hola! Encuentre el vinculo al archivo de Excel a continuación 😄 exceed.hr/wp-content/uploads/Coalesce.xlsx

@kartik-wi2jv 2 жыл бұрын

Excellent explanation, can you help me, how we need to refer to the previous row value in power query, for example, calculate date difference of current date and previous row date.

@kndeepak 7 ай бұрын

Add and index column and refer to current row index -1