Correction: when doing the first timing exercise I say "microseconds". I mean "milliseconds"! :)

Applying the function to each year doesn't work for me somehow. It gives me " ValueError: cannot insert Year, already exists " . Otherwise great series so far though! Maybe an update since it has been rolled out to current channel?

This series is great and I marvel at your depth of knowledge. Please do more videos on data science.

Great explanation. But why copy formula in each cell in second example is bad?

Great! Thank a lot! Could you share source of code and excel file 🙏

Great video Owen, I have learn so much with a single video 😁 Great job 👏

Great video!! Algorithm showed me this video now and I got in because your name rings a bell. Maybe from a forum but I do not know which one. Anyhow, liked, subscribed ✌🏼😉 And for a solution, there is one very simple, any lambda helper functions free: =LAMBDA(n, t, k, LET( c, SEQUENCE(, MAX(t)), i, t >= c, HSTACK(INDEX(n, TOCOL(IFS(i, SEQUENCE(ROWS(t))), 2)), TOCOL(IFS(i, c) + TEXTBEFORE(k, "-") - 1, 2)) ) ) n, names ; t, tickets nr. ; k, range

how I can get the dataset

Thanks, it's amazing!

Achei que o local para digitar o código, fosse algo como o VBE.

That is awesome man . Thank you

I did it in Excel and I constantly asked myself how do I do the same in SQL?, and you solved it very easily, thanks ... this video is GOLD

I get the following error when running the Python code "ImportError: Missing optional dependency 'lxml'. Use pip or conda to install lxml." I went over the code multiple times to make sure I did not type something wrong. Help please.

Just subscribed to the channel. Thanks for posting this very interesting playlist about Python in Excel.👍

another emulation of MAP in this case: =(A2:A6="a")+(B2:B6="a")>0

I have tried to learn this in the past through videos and books, no one has presented it as well as you have. Thank you, Sir.

Thank you, excellent explanation of MAP.

The best

I am impressed! How do you know the SINGLE trick? Thanks for sharing! PS: SINGLE works on a French implementation. PPS: the PIVOTEVERYNROWS function doesn not need the "n" argument which could be calculated by COUNTA(UNIQUE(attribute)) (unless I am missing an other usage of the function) Thanks again

The statement df['Team']=df['Team'].replace('','No team') does not work for me. It produces a "None" instead of 'No team'

I don't know what happened, but pivotby is not taking text as a value anymore, at all. Error values only.

*promo sm* 😱

Just Great application of reduce

Thank you soo much❤

Thanks for TELLING us what the challenge is. I realize that letting us know where the FREAK to download it is classified. SHHHHHH! 🤪

How to install the Python libraries which are missing in the Python for Excel?

You are the man!!!! I don't know why, but you had 36 views and only 5 "Thumbs Up." 😎

Thanks for share know how, in my case, I had to use xl("tripdata[#ALL]), because odbc + SQLite don't retrieve headers.

Your videos just seem to pop up on my KZbin recommendations for some reason. I was wondering if I could make a request? I worked all day yesterday on Excel Bi's Power Query Challenge 161 using Chat GPT4, Data Analysis and Chat GPT 4 could not come up with a Python solution to get the listed results. I also noticed that very few regulars that answer challenges did not submit a solution. Usually, ChatGPT4 comes up with a Python solution in one or two tries, but not this one. I know you are busy, so if you do not have the time, skip it.

You always seem to provide something interesting. Thanks for sharing.

really a nice video, wish see more about this topic. it is very useful for our analysts. many thanks.

Thanks for keeping us up to date and doing so in such a clear and realistically used way

Good.

I learned a lot from your video, but I found your solution a bit complicated. I developed a LAMBDA not for just one line, but for the entire data set: =LAMBDA(DataTable, LET(NM,INDEX(DataTable,,1), NT,INDEX(DataTable,,2), RN,INDEX(DataTable,,3), Names,TOCOL(IF(SEQUENCE(1,MAX(NT))<=NT,NM,#N/A),3), Range,TEXTSPLIT(TEXTJOIN(" ",,TOCOL(MAP(NT,TEXTBEFORE(RN,"-"), LAMBDA(a,b,TEXTJOIN(" ",,SEQUENCE(1,a,b)))),3)),," ",1), Result,VSTACK({"Name","Ticket Number"},HSTACK(Names,Range)),Result))(B9:D14) 🤗

would u mind gv us the regular expression script?

I don't know if tried this or not but here is a response from ChatGPT4. Without seeing the entire code and the environment in which it's running, I can't give a definitive answer, but I can suggest some common issues that might prevent the documentation popup from appearing when a function is called: Function not loaded properly: If the function is not loaded into the Power Query environment properly, the documentation will not trigger. Syntax errors: There may be a syntax error somewhere in the documentation metadata that's preventing the popup from working correctly. Power Query Version: Ensure that you are using a version of Power Query that supports function documentation popups. Older versions may not have this feature. Incomplete documentation metadata: All required metadata fields must be properly filled out. Missing information might prevent the popup from appearing. Incorrect function call: If the function is not being called correctly, the popup won't appear. Ensure that you are using the correct syntax to invoke the function. Context of use: Some Power Query interfaces might not support popups, or there might be settings that disable popups. Environment issues: There could be an issue with the specific Power BI or Excel environment you're working in, including disabled features or restrictions. Here are a few troubleshooting steps you can take: Double-check the syntax and completeness of your documentation metadata. Try calling the function in a new Power Query window to see if the issue persists. Check for any updates to Power Query or the host application (Excel or Power BI) and apply them. Look for any environment-specific settings that might be affecting the display of popups. If you can provide more context or additional code, I might be able to offer more targeted suggestions. I am really not qualified to ask the proper prompts here, but maybe something in here you can make sense of. Good Luck with it.

How can I use in office 2016

Is it available already?

Any chance you can show us how to extend this to do =BYCOL() ? I've got a table of numbers, and would like to do rolling calculations for each column (using a single range array of course).

당신이 제시하는 솔루션들은 다른 어떤 영상들보다도 앞서있습니다. 인사이트를 주셔서 감사합니다. 한국의 구독자로부터

That would make a nice 5G function 😃

Really informative. Thanks for sharing, I was thinking of another scenario in which we don't miss attributes but they are not in the same position. based on your amazing explanation this came to my mind for this scenario: = LAMBDA(attribute,value, LET( unique_attributes, UNIQUE(attribute), attribute_ids, SEQUENCE(ROWS(unique_attributes)), col_id, XLOOKUP(attribute, unique_attributes, attribute_ids), row_id, MAP(attribute, LAMBDA(a, COUNTIF(TAKE(attribute, 1):a, a))), pivoted, PIVOTBY(row_id, HSTACK(col_id, attribute), value, SINGLE,0,0,,0), DROP(pivoted, 1, 1) ) )

Great stuff, but REALLY needs sample download, even with just the starting data.

Great Video!!! I really enjoyed your lecture and acquired some skills from your course. Could you please always provide the raw data for your followers to practice.? If so, you will be extremely popular on the KZbin then.

I receive connection errors when I am trying to connect to localhost with Flowise or Local LLM APIs. In Python it's ok, but with excel it's not. Any thoughts?

This is terrific, I had no idea you could return a lambda in a lambda! This opens up so many possibilities. Thanks. As a minor quibble, I find the index syntax function( index( x, b - window +1, 1) : index( x, b, 1) a little offputing, and I think it would not work if the input was a generated array instead of a range, so I found an alternative: function( take( drop(x, b-window), window)) which works even if x is not a range Anyway, the concept is great and the explanation fantastic, thanks again

This looks very interesting. I import your gist, instead of result I got #N/A for the first 4 rows and after that only #NAME? when passing SUM to the function and inside Excel Labs I got SUM red-colored with "Unknown identifier" The formula is: =rolling.aggregate(B2:B16,5)(SUM)

Nice work.

I watch these in preparation for my work laptop to get the update in 2034 😄