Querri’s Post

Spent a little more time on Office Scripts today. 🧐 What would I recommend to people new to this language? 💡Abstract your frequently used code into a function. Here's one I created today which simplifies the process of adding a formula column to an Excel Table. #data #analytics #excel #coding #typescript

VBA doesn't have a way to unpack parameter arrays. 🥲 There's also no simple way to lift a user-defined function over pairwise arrays. It's possible, but something about the result feels off. It was only after digging into this for a while I realised that it was totally unnecessary. 🙃 All of this inspired by a discussion between Mark Proctor and Rick Rothstein. and Mark's interesting blog post which is well worth a read: https://lnkd.in/gW7Ar8cQ #data #analytics #excel #vba #coding

🔥 Tired of battling complex data processes in Excel? 📊 Struggling with VLOOKUPs and INDEX-MATCH functions? 🤯 Is your reconciliation file turning into a monstrous time-eater that slows down your progress? ⏳ Feeling drained by data crunching, with no time for insightful analysis? 🚀 🔍 Look no further! Reach out to me for a game-changing solution. I specialize in automating mundane tasks, freeing you to focus on the big picture. ⚙️ Harness the power of Power Query, Power Pivot, and Python to turbocharge your workflow! 💡 🛡️ Say goodbye to pricey support contracts and licensing fees. My robust systems are designed to be future-proof, so you can breathe easy. 📩 Drop me a message, and let's discuss how we can revolutionize your data process. Remember, no cure = no pay! 🌟

Have you created your own data types? Have you ever wondered about converting user-defined types to primitive types or other data formats? It's not only possible, but it can also be made seamless and elegant with the right techniques. Enter casting functions and the magic of constructors! Casting Functions: These functions, often overloaded operators, allow you to convert your custom data types into different types effortlessly. It's like having a translator for your data! Constructors for Casting: The real game-changer here is having appropriate constructors in your class. These constructors can serve as automatic converters between your custom type and other formats. It's like having a built-in transformation toolkit! Let's dive into a simple example: Check the code snippet With this setup, you can effortlessly convert a Time object to an int and also an int to a Time object even without a user defined conversion function. Isn't it fascinating how custom data types can be so flexible and versatile? So, whether you're working with dates, complex numbers, or any other custom data, remember that the power of type casting and constructors can make your code cleaner, more intuitive, and ultimately more powerful. Stay tuned for further updates. DM or comment for any discussion on such interesting topics #CodingLife #CustomDataTypes #ProgrammingMagic

Use this UDF - Function ExtractResults(inputString As String) As String  Dim i As Integer  Dim charCount As Collection  Set charCount = New Collection  For i = 1 To Len(inputString)   On Error Resume Next   charCount.Add 1, CStr(Mid(UCase(inputString), i, 1))   On Error GoTo 0  Next i  Dim result As String  result = ""  For i = 1 To Len(inputString)   If charCount(CStr(Mid(UCase(inputString), i, 1))) > 1 Then    Exit For   Else    If InStr(result, Mid(inputString, i, 1)) = 0 Then     result = result & Mid(inputString, i, 1)    End If   End If  Next i  ExtractResults = result End Function

In Excel, expand all combinations of multiple columns！ 😃 In the following Excel table, column A contains codes and the other columns are grouping columns having different meanings and containing comma-separated values. 🤣 The computing goal: split each grouping column value to generate a row for each unique combination. Below is the expansion result of the first record: Solution 😂 Use SPL XLL to enter the following formula: =spl("=E@b(?.(~.(~.split@c())).conj(eval($[xjoin(] / ~.($[~(] / # / $[)]).concat($[;]) / $[)])))",A2:G4) Explanation: E@b()function converts each row, except for the column header row, to a sequence. split@c splits a string into a comma-separated sequence. conj() function concatenates members of each sequence. eval()function takes the string as the dynamic code to execute. xjoin() performs cross product on multiple sequences to combine them. $[;] is the simplified form of writing a string, which is equivalent to "";"". The solution is so easy! Good tools make you fly fast！ If you find SPL is interesting, You can download a trial, it is currently free. 🤩 Join our group: https://lnkd.in/gyW-YFer  

Excel's Advanced Formula Environment can debug your custom functions. 💡 After seeing a post this morning from Marcus Syben showcasing a VBA UDF to calculate the volume of a cone, and noting that the value of Pi was hardcoded, I wanted to check that I recalled correctly that there's a PI() function in Excel (there is) and of course figured I'd create a LAMBDA for the same. CONEVOLUME = LAMBDA(radius, height, PI() / 3 * radius^2 * height); This is not to say that VBA UDFs don't have a place. They are still incredibly useful in pre-M365 deployments. Creating a LAMBDA instead of a UDF is not news in and of itself, but what is interesting is that the debug pane in the Advanced Formula Environment can correctly pick apart the LAMBDA function and explain what's happening. I maintain that this is a massive improvement in debugging capabilities and if you were on the fence about the AFE or LAMBDA development in the past, now might be a good time to reconsider. #data #analytics #excel #debugging

This week on my Youtube channel I covered data cleaning in R-studio. The packages used are: 1. readr 2. tidyr 3. dplyr The video covers how to use the following methods; 1. duplicated() combined with table() to display duplicated data in a table 2. distinct() to remove duplicates 3. colnames() to display column names 4. head() to display the first 8 rows of the data set 5. gather() to reshape the dataset by unpivoting columns 6. str() to check data types 7. separate() to split a column based on delimiters and so much more. Get to know how they are all used to clean the dataset by doing it yourself using the data set provided on the video description. #Rprogramming #Datacleaning #DataFam

--- CAN YOU SOLVE THIS - EXCEL CHALLENGE 333 --- -Daily Challenges @ 4am UTC | Excel: Mon to Fri | Power Query: Sat & Sun- (Solutions in any language are also welcome for Excel Challenges) Extract the string before any alphabet is repeated. Hence, in case of "Apple", the answer is "Ap".  In case of "Debt", no alphabet is repeated. Hence answer is "Debt" Post answers in Comment. (Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently) Download Practice File - https://lnkd.in/ddw7fh5s #excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r