Search results

The inner help of Excel does contain simple examples, that gives you a starting point of understanding what both functions do. And here https://chandoo.org/wp/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/ -->> The title being, but the text in the link is about offset. Mr...

Load that text file via Power Query (very similar to text import in this case, but better) and load as pivot. You could load to data model too, and actually you can use the implicit measures like you would with an ordinary pivot. Even though I'm cursing in church now. No direct need to learn...

Is this an assignment of some sort? You can't expect us to do yours, now can you? Else, how would you learn?

Get data -> From File -> From SharePoint Folder Paste in the site path (the root) In the Source step in the formula bar Change SharePoint.Files to SharePoint.Contents. Drill down on the correct library, and filter later the correct folder. You will see something alike as the screenshot, where...

Because CONCAT(Table1[Plant];Table1[[Material ]]) will create a single string: "1002100310041005100610071008ABC123ABC124ABC125ABC126ABC127ABC128ABC129" Whereas "&" creates an array of values: {"1002ABC123";"1003ABC124";"1004ABC125";"1005ABC126";"1006ABC127";"1007ABC128";"1008ABC129"} that's...

Do you mean, XLOOKUP([@Plant]&[@Material];Table1[Plant]&Table1[[Material ]];Table1[Value];"")

If you have Office 365, you can use a combination of UNIQUE and RANDARRAY with a couple of other functions. [G7]=UNIQUE(staff3[Physician]) [H6]=TRANSPOSE(UNIQUE(staff3[Note Type])) [H7]=BYCOL($H$6#;LAMBDA(Note;LET( data;FILTER(staff3[Medical Record Number];(staff3[Physician]=G7)*(staff3[Note...

Maybe simply look at the blog post: https://chandoo.org/wp/how-to-get-non-adjacent-columns-with-filter-function-in-excel/

ilyes4205, See if this works for you. From the queries and connections pane, you can copy and paste the query into the Power BI desktop in the Power Query UI. The helper list I created, will be copied too as it is used in one of the query steps. Just change the original path again to yours in...

Hi, You can do a group by on work date and technician name, and use aggregate All Rows. That gives you a sub table to work with. Now you can apply some logics on that table. -> Table.FindText(AllRows, "Holiday Worked"), will give you a record -> Table.FindText(AllRows, "Holiday - "), will...

Thanks for the feedback, Paul. Always nice to see people are happy with the provided solution or information.

Glad to have helped. Thanks for letting us know it's working.

This seems to work =D2&TEXT(COUNTIFS($D$1:D2;D2;$C$1:C2;"<"&C2)+1;"0000")

Filter in the UI will work, Paul. Would be strange if it doesn't. If the filter can be applied earlier, then the job for Power Query becomes easier too.

I'd say it's both, while number of columns have more impact. Like @Chihiro said "vertipaq engine is optimized for vertical compression.". Vertipaq optimizes data in different ways, but having less unique values generally helps. That's also why granularity is important too (also said by...

Why those long formulas, I wonder? You have clearly a modern version of Excel if not 365 since you have filter and vstack. Consolidating sheets is rather straightforward with Power Query and using a dynamic filter on current/previous month is build in too. No need for manual delete and insert...

Care yo explain what is "OT"? It's best to never use abbreviations in threads. We are from different countries and often non-native English speakers. Jargon can have many meanings in many contexts. It makes helping each other more difficult than it needs to be.

How proficient are you with M-code, Sambit? My steps are explicitly names, so that guides you step by step. However to do the same, you need to understand at least how to tweak the code. I must admit, that since a few years, I do not spend a lot of time any more on the forum. I simply do not...

That's what I meant in # 4 and #6, but had to leave work, and had no time to think further. But you did, @bosco_yip ;-) And thanks for the tip!

Never good to be in a hurry, but C/OV gekocht includes the string OV gekocht. That's why I found 2 results. Using small works in the current set-up, but there might be other cases?

@Belleke , are you on version 365? Maybe this? =INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($E$2:$E$7)-1)*(SEARCH($E$2:$E$7;E11)^0);1)) But something weird happens for the second example. I have 2 hits in the list. I did not anticipate it and I need to go now.

My messy work in progress file.

Is "Y" a vowel? As it depends according to this source. I do not have time to fully commit to a single cell solution, but got this far. Split each string into single character. =MID(A2;SEQUENCE(LEN(A2));1) and =MID(B2;SEQUENCE(LEN(B2));1) Then filter for vowels and consonants. You have to...

With wildcards. Something like if ( E11 = "*C purchased*".

I do not know of any networkdays-alike function in Power Query. Having said this, with some creativity it can be created with list operators for sure. Google it and you may find some custom made functions by MVP or Power Query experts. DAX knows the function and as far as arguments go is quite...