each & underscore_ in Power Query Explained

Поділитися
Вставка
  • Опубліковано 24 лис 2024

КОМЕНТАРІ •

  • @amitk1208
    @amitk1208 2 роки тому +23

    The best part of your teaching is you deep dive and great explanation and make the things easier to understand

  • @navisalomi
    @navisalomi Рік тому +6

    Thanks so much. It's good to know both the each syntax sugar and the declaring a variable like (r)=>. There are cases where you're nesting functions. Once you use each _ once, if you use it again , it overwrites the first one. So, be careful. If you must use each again, declare the variable.
    Example, Table.AddColumn(Source, "NewCol", each {_[Amount]} & List.Transform({2,3,4}, (x)=> x * _)).
    You can't use the each _ in the List.Transform function. You will overwrite the first _ and cause the functions to crash.

  • @MichaelBrown-lw9kz
    @MichaelBrown-lw9kz Рік тому +5

    This is by far the best explanation I have seen regarding "each" and "_". I finally understand this. Thank you so much

  • @konigkakfas3392
    @konigkakfas3392 5 місяців тому +4

    Short into the point - cool, thx!

  • @pmajoros
    @pmajoros 3 місяці тому +1

    once you have nested each's, power query starts to get confused about what you mean by _, so you better start using named functions

  • @subjectline
    @subjectline Місяць тому

    Great work! The Microsoft documentation on this is very difficult to navigate and understand, and this is clear and practical.

  • @EmpyrialArchangel
    @EmpyrialArchangel 7 місяців тому +2

    THANK YOU! I spent HOURS reading documentation, but all I needed was 10 minutes on UA-cam. You're my hero!

    • @dc2guy2
      @dc2guy2 2 місяці тому +1

      fr sometimes Microsoft's documentation seems intentionally unhelpful...dare I say obfuscated.

  • @arunkumarkesavan6077
    @arunkumarkesavan6077 2 роки тому

    Now I cant say to anyone that I am good in Power BI after watching your vidoes!!! you're the person to say it...

  • @iantotheh
    @iantotheh Рік тому +2

    Such an eloquent explanation of something I've been wondering for a while!

  • @josecarlosconejo5724
    @josecarlosconejo5724 Рік тому +1

    You are really good at explaining things… 👍

  • @ajayv304
    @ajayv304 Рік тому

    I watched this video a few months back and I listened to it now. Understood clearly.
    Thanks a lot.

  • @mohitoshbiswas4797
    @mohitoshbiswas4797 Рік тому

    Great explanation of each and _ keywords and their applications. Thanks Chandeep 👍

  • @wayneedmondson1065
    @wayneedmondson1065 2 роки тому +6

    Awesome explanation Chandeep! I think of each as shorthand for the function (myVar)=> and underscore as the variable myVar, as in (using your example): (myVar)=> myVar[Amount] to extract the amount from the record. I believe it is confusing to people because they experience each and _ before learning to formally declare functions. If they learned the formal way first and then learned the shorthand of each and _ it might be easier. Thanks for a great explanation.. alway good learning at Goodly! Thumbs up!!

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому +4

      I am with you a 💯 percent. That is also because the UI generates the shorthand code and that is what people see and try to understand.😁
      Personally I feel the each _ is more humanly to read.

  • @kiran521
    @kiran521 2 роки тому

    Thanks Chandeep, please make video on problems occur in bidirectional direction and why it is not feasible

  • @justinwduff
    @justinwduff Рік тому

    I know very little about M, but how you explained this made a lot of sense. Thank you.

    • @GoodlyChandeep
      @GoodlyChandeep  Рік тому

      Thanks Justin :)
      To learn more about M, You can refer to this video.
      ua-cam.com/video/5s8Ky5r43uI/v-deo.html

  • @roywilson9580
    @roywilson9580 2 роки тому +1

    Thanks for the interesting video. I wish someone had explained this so clearly when I first tried to work out what the each and _ were doing and how to use them.

  • @williamarthur4801
    @williamarthur4801 2 роки тому

    Thank you, its' about time soneone tackled this topic, I rememeber when I first started looking at M being very cofused by the Fuction (_, , that I would see. I still find there use confusing / interesting in that sometimes using a named function eg (A)=> A[whatever], will work whereas each [whatever] , might not.
    Thank you for all your work in putting these videos together.

  • @mohamedfouad7958
    @mohamedfouad7958 Рік тому +1

    Love you man, I learned a lot from you and still learning

  • @larmondoflairallen4705
    @larmondoflairallen4705 2 роки тому

    My prayers have been answered. Thank you, Chandeep!!!!

  • @oscarmendez-roca9181
    @oscarmendez-roca9181 2 роки тому +5

    Great video and explanation with simple examples. I suggest to continue with the use of double "each" in case of working with arrays (List of a List). Thanks Chandeep!!

    • @Vandalfoe
      @Vandalfoe 2 роки тому +1

      Yes, please. I'm struggling to know how to work with 'each' row of a table, say, if one is added from a merge, for example. How do I access the "outside" underscore?

    • @MrDonkey912
      @MrDonkey912 Рік тому +1

      @@Vandalfoe If you name your variables, you can access both, ex:
      = Table.AddColumn(
      s, "n",
      (row) => List.Select(
      l, each _ = row[Name]
      ), type any )

  • @zahoorsarbandi2982
    @zahoorsarbandi2982 2 роки тому

    The explanation of "each" and "_" has been been done in a very simple and effective way... zabar10

  • @Hello-bn2yc
    @Hello-bn2yc 10 місяців тому

    Finally I was able to get it... Thank you so much Chandeep.

  • @Bic2705
    @Bic2705 5 місяців тому

    I have a lot of difficulty creating a conditional transformation that involves more than one column. I would really like a video about it.

  • @IvanCortinas_ES
    @IvanCortinas_ES Рік тому

    Superb explanation. It can't be explained better. Thank you Chandeep!!!

  • @rpopecpa
    @rpopecpa 8 місяців тому

    Thank you for this video!! A simple bit of valuable information to add to my knowledge base.

  • @elikent78
    @elikent78 2 роки тому

    spent an hour trying to understand this from someone else's lesson yesterday, and couldn't get it. but you, in ten minutes, done. fantastic, chandeep.

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому +2

      Hey Eli, I spent an hour in planning the video to explain in 10 mins 😁
      I am glad it helped.
      Cheers

  • @anais4766
    @anais4766 Місяць тому

    wow! eye opening! Thank you!

  • @jon99long
    @jon99long 5 місяців тому

    I’m a power query amateur, just playing around in my spare time, trying to find solutions to various data problems (mostly accounting.) I have spent many hours looking at your videos over and over again. At first most of them didn’t make sense, but I’ve suddenly had many “eureka” moments rewatching a lot of your videos. This has given me a lot of confidence to try more challenging problems. Thanks for some great lessons.

  • @QuantumIdeas
    @QuantumIdeas Рік тому

    Fantastic video, Chandeep! Each, _, () and => have been so mysterious to me before viewing this video. This is a huge for me.

  • @komanguy
    @komanguy 2 роки тому

    Great! Very well explained, the meaning of the underscore and how to define a function. Thank you very much.

  • @ramkumarsai9846
    @ramkumarsai9846 Рік тому +1

    Hi. I loved your way of explaining and I have learned lot of stuff from your video’s.
    I have strucked some part of the work like dynamically comes column headers from the row data. In power BI. Could you please help me on the same.

  • @johnfunet
    @johnfunet 2 роки тому

    Very interesting video, thanks for the excellent way you explain step by step. Very professional.

  • @GF86123
    @GF86123 3 місяці тому

    This is, which is nothing, but a great video!!

  • @isidreb
    @isidreb 2 роки тому

    A great explanation, using examples that are easy to understand. Thanks for sharing!!

  • @karimallahwala7022
    @karimallahwala7022 2 роки тому

    You teach great Knowledge. Very helpful thank so much.

  • @seamushand8439
    @seamushand8439 2 роки тому

    Thank you Chandeep, very well explained

  • @Faisal63045
    @Faisal63045 2 роки тому

    Thank you for sharing this info if there's any example from the real world scenario it would be great

  • @jaymehta3320
    @jaymehta3320 2 роки тому

    Thanks for sharing this. Haven't used each and _ that much but this helps to encourage and see where I can use it to make the queries more efficient/effective.

  • @ajieapen
    @ajieapen 2 роки тому

    ah... I've been waiting for this since ages... No words to thank you, Chandeep... :)

  • @OmisileKehindeOlugbenga
    @OmisileKehindeOlugbenga Рік тому

    Thank you Chandeep. Very simple to follow and remember.

  • @ΠαναγιώτηςΧατζηαντωνίου-ι7θ

    Hi, thanks for the great explanation. A little crazy question... Is there any way to turn PQ editor in black theme as you did?

  • @EricHartwigExcelConsulting
    @EricHartwigExcelConsulting 2 роки тому

    Great video and really detailed explanation in the use of the "each" and "_".
    Thank you for making this video!

  • @vacilando86
    @vacilando86 2 роки тому

    Finally, It's understood 😄 , Thanks for your endless effort.

  • @bhaveshh5786
    @bhaveshh5786 2 роки тому

    Hey Chandeep, i want to calculate turn around time if a transaction received after 3pm it should consider next day to calculate it with close date. Also it should only consider the business days excluding Saturday and Sunday. Thanks in advance.

  • @seanys
    @seanys 4 місяці тому

    I wish I had seen this video when I first started trying to use Power Query.

  • @kerryhaxton1980
    @kerryhaxton1980 Рік тому

    Thanks for you time to help us along. I have an each and underscore that I'm trying to resolve. and maybe getting myself twisted up: I'm working on a sheet, and I want to change every occurrence of a character (% in my case) to a VALUE (Happens to be the Column header). I have the column names to which I'm applying the transformation in a list (Collist), and think I want to apply a process like
    List.Transform(Collist, each {_, each Text.Replace(_,"%", XXX)}))
    But I want the XXX to be represented by the same value from Collist.
    In my test sample Collist contains 3 values:
    When I change XXX to a literal, the process works, and all the "%" values are changed to "XXX".....but in real like I have a number of nested tables, with varying number/names of columns over which I need to perform the transformation:
    Sorry long winded question

  • @alexandresalome2684
    @alexandresalome2684 9 місяців тому

    Excellent explanation, thank you!
    I have a question: is it possible to change the value of a simple item in a column, based (table.transformRows, each , if, then, else) on another column, without adding a new column?
    example: [Name] = a,b,c and [Years] = 10,20,30
    if [Name ] = c then [Years] = 40 .
    (without using "table.addcollumns", that is, changing directly on column [Years]?)
    If yes, Is it possible create a video about it?

  • @skimpylemon8034
    @skimpylemon8034 2 роки тому

    Fantastic video with a excellent explanation! Thank you Chandeep!

  • @stephanedelaval6525
    @stephanedelaval6525 Рік тому

    Clear and neat. Thank you for the explanation

  • @stefanoconti4472
    @stefanoconti4472 5 місяців тому

    Thank you very much, excellent and very clear explanation. Your videos are very beautiful and really helpful for everyone.
    But please try to speak SLOWER, your spoken English is too fast, sometimes the words overlap and it is not clear what you mean. Thank you

  • @xaft
    @xaft 6 місяців тому

    Love your content and short explanations. Pls let me know if u have got discount for ur courses!!

  • @mohitupadhayay1439
    @mohitupadhayay1439 2 роки тому

    Chandeep praji
    Real time DAX scenario ki playlist bana dijye for interview.

  • @rohanbhattacharyya6600
    @rohanbhattacharyya6600 Рік тому

    Very informative. Thanks!

  • @joaovasconcelos5360
    @joaovasconcelos5360 2 місяці тому

    Amazing, thank you!

  • @anilyadav-rt4sr
    @anilyadav-rt4sr 2 роки тому

    Thanks Your power query video is too good

  • @kamleshmistry2728
    @kamleshmistry2728 Рік тому

    hats off. thanks for great content.

  • @briandelaney6354
    @briandelaney6354 2 роки тому

    Thanks for this explanation - very clearly explained 👊

  • @brij26579
    @brij26579 2 роки тому

    Awesome explanation & clarity 👌👍

  • @avecNava
    @avecNava Рік тому

    each seconds of this video is great :)

  • @OdelLeal
    @OdelLeal 2 роки тому

    Great job and very helpful your information. Súper.

  • @KuldeepSingh-nq1vi
    @KuldeepSingh-nq1vi 2 роки тому

    Most awaited Video, Thank you so much that you created video on this topic and very nicely explained each and "_" keyword. It will help us to understand complicated M code..I appreciate your dedication towards teaching us..we would expect other basic videos like this from you.. Please make video on how to create custom functions in power query from scratch..

  • @RobertoStaltari
    @RobertoStaltari 2 роки тому

    This video deserves a double thumb up!

  • @oliverscheurich9202
    @oliverscheurich9202 2 роки тому +1

    Great. You should have 320.000 subscribers instead of 32.000.

  • @pmsocho
    @pmsocho 2 роки тому

    Great explanateion! Thanks!

  • @90hsilva
    @90hsilva Рік тому

    very clear as usual mate, thanks :)

  • @balakrishnaj1713
    @balakrishnaj1713 2 роки тому

    This video is very helpful, thanks a ton 😀

  • @malchicken
    @malchicken 2 роки тому +1

    Very well explained thank you😊. When you declared (r) as a variable, could you have declared other variables that *don’t* represent the current record value? How complex can the functions used be? Does the variable always have to represent the current record? 🙏

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому

      The variable name can be anything but you can only declare a single variable while you are working with records in a table.

    • @Vandalfoe
      @Vandalfoe 2 роки тому

      I believe that in the content of list.Xform and table.addColumn, that third parameter is being "fed" each (no pun intended) row/item of the first parameter.
      Therefore, whatever variable name you use, it will be loaded with 'each' item. @Goodly, is that about right?

    • @MrDonkey912
      @MrDonkey912 Рік тому +1

      table: `_` is the record of the current row
      List: `_` is the current list item when looping
      Table.SelectRows: `_` is the record of the current row
      You can write your own functions, that validate better
      Here's two examples that assume your table has a column named "Number" which is a number
      = Table.AddColumn(
      Source, "Enforce Text",
      (row) as text =>
      Text.From( row[Number] ) & " ending", type text)
      = Table.AddColumn(
      Source, "Enforce Int",
      (row) as number =>
      row[Number] + 100, type number)
      the `as number` is a type assertion. Either the result is a number, else, that row throws an error.
      plus the columns datatype is correct! It doesn't reset to type any, because `each` is what caused it to lose the type

  • @freedomdst
    @freedomdst 6 місяців тому

    can you put certain columns in a record and use it to show info on a regular tooltip? I wanted to show certain info depending on type of data, i had to concatenate columns based on conditions and even set titles for the data, like for data A "info1: " & "[info1)] & " " & "info2: "& [info2]... and so on.

  • @matheusosa
    @matheusosa Рік тому

    Great explanation!

  • @Faisal63045
    @Faisal63045 2 роки тому

    Thank you for sharing this, is there a way to track values changes after refresh a power query in excel

  • @cooolbreeze
    @cooolbreeze 2 роки тому

    Really well explained.

  • @KirillBezzubkine
    @KirillBezzubkine 2 роки тому

    I was wondering what "_" means. But actually i found more convenient to use " (x) =>" construction

  • @matheussvs
    @matheussvs Рік тому

    Thanks man! Youre amazing

  • @dilip1able
    @dilip1able Рік тому

    Awesome Chandeep

  • @vadnerepravin
    @vadnerepravin 2 роки тому

    Question regarding power query
    I have many coulmn like Printing and Stationary 18%, repairing 18%, Consultancy 18% like that
    I want to Addition all this coulmn in Net 18%
    I have more than 200 ledger above said example. I have made table mentioned all ledger in left side like (consultancy 18%) and summation in coulmn right side like (Net18%)
    How can we dynamically addition above coulmn

  • @alvarorodriguezlasso
    @alvarorodriguezlasso Рік тому

    You are a crack, regards from Cali Colombia

  • @LaloinLondon
    @LaloinLondon Рік тому

    Thanks for sharing. Would you mind telling me how could you get amount without using the name 'Amount', just by position?

  • @kennethstephani692
    @kennethstephani692 2 роки тому

    Great Video as usual, Chandeep!!

  • @gennarocimmino
    @gennarocimmino 2 роки тому

    Thanks very much

  • @nishant77goel
    @nishant77goel 2 роки тому

    Help needed!
    Selected Parameter = maxx(filter('Date Value', 'Date Value'[Date Value Order] = SELECTEDVALUE('Date Value'[Date Value Order])), 'Date Value'[Date Value Fields])
    This returns the name of selected column name based on the selected Parameter.
    Now based on the selected column name wants to calculate dates that falls in last 180 days on that partcular column.
    Last 180 days = IF([Selected Parameter] > (TODAY()-180),"1","0") This is giving error that the values/ Format are not matched one is date and other is text.
    As of now the dates column are in date time format.

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому +1

      try wrapping the VALUE function around TODAY()
      VALUE ( TODAY () ) -180 ....

    • @nishant77goel
      @nishant77goel 2 роки тому

      @@GoodlyChandeep this is not working. Value coming from selected parameter is not considered as column but is considered as text. So it's not matching up with today()-180 days condition. If I directly consider column the formula is working fine

  • @mmuthuvel6829
    @mmuthuvel6829 Рік тому

    Very clear explanation. Do you have any course in Udemy?

  • @luigibru8677
    @luigibru8677 Рік тому

    General question: any idea how to turn on in MacOs the syntax helper?

  • @FRANKWHITE1996
    @FRANKWHITE1996 2 роки тому

    Thanks for sharing ❤

  • @muralidharannatarajan4251
    @muralidharannatarajan4251 Рік тому

    Super explanation

  • @Moellz
    @Moellz 2 роки тому

    Hello Chandeep,
    great video. I really like the short explanations about one topic that you're posting recently.
    One question: Why does "r" in the section about alternate to each_ hold every record of the table? Is it because of the name?

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому +2

      the variable name could be anything (r, x, abc, _)
      the variable holds the value of each item of the object (table, list etc..) and can be retrieved using a function =>
      So all of the following are valid
      = Table.AddColumn(Table, "ColName", (abc) => abc)
      = Table.AddColumn(Table, "ColName", (_) => _)
      = Table.AddColumn(Table, "ColName", (myrecord) => myrecord)

  • @stefaanlouette
    @stefaanlouette Рік тому

    Awesome video

  • @Геннадій-ц8э
    @Геннадій-ц8э Рік тому

    Great!

  • @sunnygala7972
    @sunnygala7972 2 роки тому

    Great video! Do you cover all these concepts in your Power query course?

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому +1

      working on an exclusive M Language course. Should be out soon :)

  • @surjaa.c
    @surjaa.c 2 роки тому

    Thanks a lot for this video! My question is, are each and _ always used together like you have shown or can there be cases where only one of the two is used?

    • @GoodlyChandeep
      @GoodlyChandeep  2 роки тому +3

      the underscore can be used in isolation if you're writing a function.
      But if you've used the each keyword, it will always be followed by _

    • @surjaa.c
      @surjaa.c 2 роки тому

      @@GoodlyChandeep thanks a lot! Please continue the 'good' work you are doing! 😁

  • @mehmetatas5459
    @mehmetatas5459 Рік тому +1

    hello godly
    I didn't understand something in a video I watched.
    ua-cam.com/video/WxWL3i05hZ0/v-deo.html)
    In the section after 09:54, he creates (OutherTable) and (InnerTable) functions in the formulas he uses.
    How can I write the same formula entirely using each without creating a function?
    = Table.AddColumn(Source,"TableCol", (OutherTable)=> Table.SelectRows(Source, (InnerTable)=> InnerTable[Amount] > OutherTable[Amount]))
    What code do I need to write when I use each instead of (InnerTable)=>?
    I would be glad if you can help
    Thanks

  • @kebincui
    @kebincui 2 роки тому

    Well explained👍

  • @Jonbooi
    @Jonbooi Рік тому

    Why can I use the _ as a shorthand for the column name in this formula if the _ holds the whole record?
    = Table.ReplaceValue(Source, each [Colum1], each if [Column1] = "AAA" then _ else "123" ,Replacer.ReplaceText,{"Column1"})

  • @thebhaskarjoshi
    @thebhaskarjoshi 2 роки тому

    amazing thanks!

  • @seamushand8439
    @seamushand8439 Місяць тому

    Hi Chandeep, I am importing a PDF file from an old accounting package and, using some of your other tutorials, I am able to strip out the tables in the PDF. However some of the tables contain only report data, eg the Date the report is issued, report name etc. For now i have been simply using table append to stacl them all together and then i manually delete the columns with the report data. I would like to be able to dynamically iterate through each of the tables ( using each), test if the data in the first record contains "Date" ( or similliar trigger word) and if so delete the table, leaving only legitimate tables for me to append. Can you point me to anything that would help with this ? Thanks. Seamus

    • @GoodlyChandeep
      @GoodlyChandeep  Місяць тому

      Parts of this video might be useful ua-cam.com/video/pTpx9jeIDx8/v-deo.html

    • @seamushand8439
      @seamushand8439 Місяць тому

      @@GoodlyChandeep Thank you so much

  • @kkhalidabed
    @kkhalidabed Рік тому

    Well Done

  • @santoshpv321
    @santoshpv321 2 роки тому

    Supreme!!!

  • @rajanpradeepankarath8846
    @rajanpradeepankarath8846 8 місяців тому

    It would perhaps be better to understand the relevance if you had put in a practical life scenario on how to use this

  • @sandeepkhawas3367
    @sandeepkhawas3367 9 місяців тому

    Can anyone suggest as getting this while using the same function in power query!
    Expression.Error: We cannot apply operator + to types Text and Number.
    Details:
    Operator=+
    Left=1
    Right=1

  • @gopalnaidu1103
    @gopalnaidu1103 2 роки тому

    I have one unsolved query .could you please help me out .if the column contains the data as names and Gmail IDs and #twitter IDs ,and some other like diffent delimeters data .how to seperate the only names to one column and all Gmail IDs ,#twiiter IDs and delimeter data in to other column

  • @markhenderson3771
    @markhenderson3771 Рік тому

    Can I add ‘ and null to return a list of lists?