My name is Gašper Kamenšek, and I've been working with and teaching Excel since Excel XP (the forgotten version :) ) and Power BI since v1. In 2016 I received the Microsoft MVP award for Excel, and I have been a part of the MVP community ever since. This channel is my way of giving back. It's all about sharing my experience and know-how with YOU to help you get better in Excel and Power BI.
Hey Gasper, I wonder if you can elaborate on this use of sequence, I need to have a sequence of numbers that have a break in them at random intervals, so I cannot use the IF <13 etc. Instead is there a way to use a symbol or a colored cell?
I was trying to do a merge between two queries in Power Query using the left outer joint. But the second query is against a column that is a merge column of 5 text columns. So, I did not find a solution in Power Query and I ended using Xlookup. The formula used was =XLOOKUP("*"&E5&"*", code,quantity, "no match", 2), where a lookup-value, as text, can match a specific text contained in the merged column with different texts separated by a space. If there is a way to do it in Power, let me know. Thanks
Hi Gasper, love your work. I'm a beginner, and I'm trying to follow your steps which you breakdown throughout the series. I'm having difficulty adding a column in bulk (12:31 in the above video). I have copied everything, cell by cell, formatting and it all works to this point. I enter: 1 calendar = ADDCOLUMNS ( CALENDAR("1/1/2017","31/12/2022"), but when I hit return, I receive an error notification starting ! Too few arguments............' what am I doing wrong?
Hi I have a question I hope you can help me Thanks in advance: My question is how to automatically number rows in sequence but per classification within the same sheet? Example: White has 10 rows, Blue has 7 rows and Red has 5 rows.
why don't you multiply by 100? in that case the index will itself be "readable". e.g. 2017 Jan will become 201701, for the months will always have the 2 decimapl places, but unlike multiplying with 12, the year number will not change. Similarly, when I want to have the whole date as a "readable number", I use 10000*y+100*m+d.
Thank you for sharing your knowledge and simple files Thank you very much... because from the video you can follow it step by step easily using example files
Look, its 25 questions on the SHL Robert Half test and you have less than 25 minutes to complete. Issues are too many options to solve and u fail. It’s bullshit! I used excel when it was shit!
You will be always remembered because of this control enter. It's such a breeze this control enter. I used to type same date or other text in many cells and copy and paste text from previous cells on cells down, and my co-workers did the same thing, you even hear them doing the same sound control comma plus enter or copy and paste on so many cells down. With control enter, not anymore this issue.
very informative video sir. that would help me a lot in working with excel..... good thing you're not the CEO of the company that i'm aiming for, or there's no way that i'll ever get hired😁
its too bad I cant biuu a copy of office 365. its just too much money for me and I don't like subscriptions. I love sorting with interactive check boxes also. . Have To use google sheets
Hello, Gasper. Thanks for the explanation. Another case is that I have hundreds of names that are repeated and ordered alphabetically. I'd like to assign a serial number ranging from "01" to "n" to each name, with the following name starting from "01" again, and so on for the remaining names. Is it possible to do this with Excel? I'm doing it manually; please let me know if there's an easier approach.
Ha! I just recently learned about working with lists and I was struggeling with a probl.... euh.... challenge where I want to know whether a hotelguest has his/her/their birthday during their stay. Because not everybody has the same arrival and/or departure date I cannot work with a general datelist for the stay. In earlier solutions I worked with dayofyear, comparing it to dayofyear in the year of birth. But that is not always accurate. When a birthday is the first of march or later in the year, and the year of birth was a leap year, or the current year is a leap year, the day is one day of. In later solutions I worked with a datelist that I expanded to rows for each day of the stay. 60 records could easily become over 400 records! With the list.intersect I was finally able to solve it quite elegant. I think. There is most probably a far simpler solution, but for now I was quite pleased with my code, inspired by this great video! let Source = ExcelCurrentWorkbook(), AgeOnDepartureDayRoundedDown = Table.AddColumn(Source, "AgeRoundedDown", each Number.RoundDown((Number.From([Departure])-Number.From([DateOfBirth]))/365.25), Int64.Type), ListOfDaysPresent = Table.AddColumn(AgeOnDepartureDayRoundedDown , "ListOfDaysPresent ", each List.Dates([Arrival], Duration.Days([Departure]-[Arrival]), #duration(1,0,0,0))), TransformDatesToDD_MMMM = Table.TransformColumns(ListOfDaysPresent , {"ListOfDaysPresent ", each List.Transform(_, each Text.Combine({Date.ToText(_, "%d"), " ", Date.ToText(_, "MMMM")}))}), ListOfBirthdays = Table.AddColumn( TransformDatesToDD_MMMM, "Birthday", each {Text.Combine({Date.ToText([DateOfBirth], "%d"), " ", Date.ToText([DateOfBirth], "MMMM")})}), HasBirthdayDuringStay = Table.AddColumn(ListOfBirthdays , "HasBirthdayDuringStay", each List.Intersect({[Birthday],[ListOfDaysPresent ]})), TransformListToText = Table.TransformColumns(HasBirthdayDuringStay, {"HasBirthdayDuringStay", each Text.Combine(List.Transform(_, Text.From)), type text}), BirthdayNotification = Table.AddColumn(TransformListToText , "BirthdayNotification", each if [HasBirthdayDuringStay] <>"" then "Birthday celebrations at " &[HasBirthdayDuringStay] &". Reaches the tender age of " &(Number.ToText([AgeRoundedDown])) &" years!" else "") in BirthdayNotification Should you have any suggestions for simplifying or otherwise; I'm open to feedback! Thanks again for this inspiring video! Martijn
very instructive video. I saw a you tube tutorial where the speaker used the name box to highlight column but it also showes the number of inches that column. do you know how that was done?
Thank you so much for this amazing tutorial. I learned so much from it it's remarkable! In 40min i upped my data skills 10 notches. It helped consolidate what i knew of arrays into an applicable real world scenario and the fact that you contrasted and compared the more "traditional" or antiquated ways to solve those problems in Excel with the latest and hippest was really useful. My only regret is that you didn't include a link to a test question file and more crucially to the 4 text files that were transformed in power query. I literally just discovered Power query and those text files would've been a great tool for practice. Anyway, fantastic job and i am now
I can't believe you couldn't come up with a less convoluted and more common use case as an introduction to lists. If you have to take 5 minutes setting up the scenario, you've chosen a bad scenario. Do better.
What if there are blank arrays, item, description, qty, unit price, total price are the columns, description is on multiple arrays within 1 column, i.e a BOQ of construction company
Wow man. Thank you, Thank you, Thank you. I have been cramming excel video tutorials for the past week. Your videos are exactly what I was hoping to find. Thank you, I can tell you really enjoy excel. It makes learning a lot easier. :)