Пікірлер
@rinmai9225 9 сағат бұрын
Tks Melissa de Korte. Very useful
@txreal2 2 күн бұрын
Awesome! So many useful tools to monkey around: - Crtl +T and table name prompt - BiblioMonkey - store custom Queries, Measures, Formulas ... ...
@skillwavetraining 2 күн бұрын
Thanks!
@luisinaflores8914 3 күн бұрын
Hi i cant use the one click otimization. I have de pbip in tmdl format but still having an error when i want to use it. Could you help me?
@PedroSmiderle-mp2si 3 күн бұрын
Hey! Measure Killer developer here, what is the error you are having?
@kirkyit 6 күн бұрын
Brilliant! Thank you for sharing your expertise.
@raimundojs9547 9 күн бұрын
Melissa de Korte is really impressive! She is a M master! Brilliant!
@davidjosevarelagarcia7011 20 күн бұрын
JUST Great , Thanks, is very Helpful
@sebfromgermany3819 20 күн бұрын
Very nice video, looks like M is your code Melissa 😂 - when transforming a single column is your recommendation to use Table.ReplaceValue or Table.TransformColumns? The „wild card option“ when working with Table.TransformColumns is amazing. I’m sure I will use this regularly. Cheers Seb
@emilmubarakshin49 21 күн бұрын
Absolutely outstanding as always! Thank you Ken for organizing and Melissa for expanding / stretching our minds infinitely
@Softwaretrain 22 күн бұрын
Thanks, It was actually "Digging for Gold" by Melissa.
@chandeepchhabra 22 күн бұрын
I'd love to see row level security added to Excel Data Models.
@KenPulsXLGuru 22 күн бұрын
For sure! I’m not going to hold my breath to see it any time soon though. 😢
@hellmutmatheus2626 25 күн бұрын
Awesome! really thank you!
@jazzista1967 29 күн бұрын
Get presentation with good tricks. PBIX file with the final solution is empty. Could you please re-upload? Thanks
@skillwavetraining 27 күн бұрын
Thanks for watching the presentation! When you open the file, go to the "Home" tab and click "Transform data", so the Power Query editor opens. You will then be able to see the 2 queries.
@jazzista1967 20 күн бұрын
@@skillwavetraining . Ok i see the queries now. Thanks
@soadindakhor3932 Ай бұрын
cool stuff…will need that
@sergeiStPetebaklan Ай бұрын
Very nice presentation. Nice samples, great logic, no overcomplication. Didn't touch cube formulae for an year or so, played a bit with sample 3 to recall the stuff slicerSelection = LAMBDA( slicer, [ModelConnection], LET( connection, IF( ISOMITTED(ModelConnection), "ThisWorkbookDataModel", ModelConnection), itemsSelected, CUBESETCOUNT( CUBESET(connection, slicer) ), CUBERANKEDMEMBER( connection,slicer, SEQUENCE( itemsSelected ) ) )); /* =TEXTJOIN(", ", , slicerSelection(Slicer_CountrySoldTo) ) */ filteredValue = LAMBDA( measure, table, field, item, [ModelConnection], LET( connection, IF( ISOMITTED(ModelConnection), "ThisWorkbookDataModel", ModelConnection), CUBEVALUE( connection, "[Measures].[" & measure & "]", "[" & table & "].[" & field & "].[" & item & "]" ) ) ); /* =REDUCE(0, slicerSelection(Slicer_CountrySoldTo), LAMBDA( a, v, a + filteredValue( "Total Revenue", "SalesYTD", "CountrySoldTo", v ) ) ) */
@roderickmose4691 Ай бұрын
Excel-lent explanation and demonstration of the function. Thanks guys!
@maciejkopczynski55 Ай бұрын
Awesome sessions! Cube formulas can be sooooo powerful when there is an appropriate scenario for it. One question: Will you or Mike provide sample files for this session? Either completed or blank? Thanks again!
@skillwavetraining Ай бұрын
We will for sure, hopefully later today!
@skillwavetraining Ай бұрын
Hi again, the example files are now available for download here: 1drv.ms/u/s!AuhhLgCXSCKKgZEAiDSqRJWzVCrMbA?e=d1vxjC
@josericardo3015 Ай бұрын
Thanks Ken & MIke for the presentation! Congrats for the initiative always! For sure I will adopt in my Power Pivot spreadsheets the tips I have learned with Mike today!
@skillwavetraining Ай бұрын
@josericardo3015, if you need any help learning Power Pivot, check out my Self Service BI Academy. It's an extremely comprehensive program that covers Power Query, Power Pivot, Dimensional Modeling and DAX (in Excel) as well as Power BI: skillwave.training/shop/self-service-bi-academy/
@kebincui Ай бұрын
super❤
@mrbartuss1 Ай бұрын
Could you please share the file so we could follow along?
@skillwavetraining Ай бұрын
We've added links to Alejandra's files to the event description, but here are the direct links: - Working Files: 1drv.ms/u/s!AuhhLgCXSCKKgZBjlZIVh2SAp_eu4w?e=lFvzt2 - Final Solution: 1drv.ms/u/s!AuhhLgCXSCKKgZBi5wPhVZu6cnH93g?e=41UOpt
@mrbartuss1 Ай бұрын
@@skillwavetraining Thank you!
@williamarthur4801 Ай бұрын
This may be an odd question but is there a way to the custom comparer to work within a nested list sorting by multiple columns eg Table.Sort(Source, {{ "Month", (x)=> x [Month] }, {"Text", (y)=> y [Text] } } will return an error, just curious.
@BIGorilla Ай бұрын
William, That's a great question. The answer is yes, and the syntax depends on the function you use. For instance, for the Table.Sort function, you can provide multiple arguments in the following way: Table.Sort( Source, { each List.PositionOf( {"Apple", "Pickle", "Banana"}, [Item] ), { "Aisle",Order.Ascending } } ) I provided an example at: powerquery.how/table-sort/ Hope that helps!
@zoranmilokanovic6229 Ай бұрын
Very useful video. If I may suggest, try replacing the hard-coded columns in the Expanded Transform Sheet step with the Table.ColumnNames(#"Sample Sheet Transform"). This is how it works with the one-click transform PQ solution that combines all files from a folder.
@skillwavetraining Ай бұрын
True enough although... while that will dynamically pull the headers from the first nested table, it won't get additional columns that appear (only) in subsequent columns. I'm not saying it isn't a good practice, you're correct that I should have done this, but I think that - if I'm concerned about dynamic column names - I'd go the extra mile and build something to look at expanding all columns from each table. ;)
@zoranmilokanovic6229 Ай бұрын
I was guided by the functionality of From File/Folder, which comes out-of-the-box. There, the header is taken dynamically from the Transform Sample File-hence the suggestion. Especially since, in your example, all the sheets have the same header. I completely agree with you that if this weren't the case, a more robust solution would be necessary. In the Output query, the existing step for expanding would be replaced with the following two steps Header = List.Union(List.Transform(#"Removed Other Columns"[Transform Sheet], Table.ColumnNames)), #"Expanded Transform Sheet" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform Sheet", Header),
@joshblackman2380 Ай бұрын
Can we use these tools against a power BI model versus an excel model?
@skillwavetraining Ай бұрын
Unfortunately the TimeSleuth can only time Excel queries. Having said that - you could use the Import Monkey to import your pbix file into Excel. 😉
@joshblackman2380 Ай бұрын
News I can use. Legend!
@AmitChristian 2 ай бұрын
Excellent seminar. I was really disappointed with the "typical" example of Copilot in Excel on KZbin search and they terribly lack any depth of insight. Most of the things those videos show, you can do it without asking an AI, like creating a profile column. This video gaves me lots of useful information and insight on how to use Copilot for more real life examples. Thanks.
@giorgiorovelli1542 2 ай бұрын
What's VanPUG Excel Ed?
@skillwavetraining 2 ай бұрын
VanPUG is the acronym for the Vancouver Power Bi (& modern excel) User Group. We host two meetups per month, and this was the Excel edition. (In two weeks we'll be hosting this month's Power BI Edition: www.meetup.com/vancouver-power-bi-user-group/events/302124161/
@giorgiorovelli1542 2 ай бұрын
@@skillwavetraining Thanks😃. Where do I find the videos for the previous VanPUG meetings?
@skillwavetraining 2 ай бұрын
@@giorgiorovelli1542 The full playlist can be found here: kzbin.info/aero/PLIRJFbuwYskLKHIWt9OBVpeMs-NZ_jvS_
@GrainneDuggan_Excel 2 ай бұрын
Thanks for the explanation of Copilot. It is improving steadily.
@yp4577 2 ай бұрын
Only 153 likes??? Impossible! Thank you! I was at my wits end
@Fernando_Calero 2 ай бұрын
Great session, thank you for sharing! @Ken if you want a great DAX Studio presenter you could contact Ricardo Rincon @nexus150
@pbiqueryous 2 ай бұрын
Wonderful and immensely useful!
@wmcnabb 2 ай бұрын
Awesome session! Thanks Sue and Ken as I was unable to catch this live due to some volunteer commitments. I have so many questions now...
@rubenmunozverdu7528 2 ай бұрын
"Too much" work. I prefer a single cell named range which you can also import to PQ.
@skillwavetraining 2 ай бұрын
Honestly I'm more likely to go named range as well. But it does provide options, which are always nice to have.
@rubenmunozverdu7528 2 ай бұрын
@@skillwavetraining don't get me wrong, it's cool to know that it can be done, even if it's just for the sake of nerdiness
@mrbartuss1 2 ай бұрын
Could you please share the file so we could follow along?
@sjaoenvf 2 ай бұрын
great explanation. thank you.
@tomharvey8613 2 ай бұрын
How do you do a Bridge table when each table had different granularity? For example, I have locations in my people data it's Building (Location ID)/City/State/Region/Country/etc. and in my recruiting system it's City/State/Region/Country/etc. Combining them into the data model on Location ID doesn't work because it does not appear in my recruiting system nor can I use city because city is not unique in my people data (we have multiple locations in each city).
@skillwavetraining 2 ай бұрын
The first thing I'd say is that this was a pretty simple example where we can do everything through a single table... but don't get stuck in the fact that it always needs to be. This is going to be hard to explain via text without images, but there are occasions where bridging 'Fact A' to 'Fact B' where you can bridge directly from 'Dim Common' to 'Fact B', but requires a table between 'Fact A' and 'Dim Common' in order to deal with de-duplicating values. There are also scenarios where you will need to create a 'composite key' between the tables in order to come up with a one to many relationship. City-Province/State is a good example of this - think of the famous "Springfield" which exists in every state in the US. So if you have both City and State columns, we can merge them (use a delimiter) to get City-State which should manifest a one-to-many relationship. Depending on the model, you may also need an additional level of bridging (or not), but these two techniques often work together in complex models. FWIW, I do cover this in my Self Service BI Academy training course. skillwave.training/shop/self-service-bi-academy/
@wasfiathhar 2 ай бұрын
Exceptional ❤
@raimundojs9547 2 ай бұрын
Rick de Groot is definitely brilliant, magistral. His knowledge and skills with the M Language are far beyond the ordinary. It's always a privilege to watch a lecture of his.
@BIGorilla 2 ай бұрын
It's always a pleasure to provide presentation to such a wonderful community. Glad to see you here too Raimundo !
@williamarthur4801 3 ай бұрын
Rick is to M what a couple of Italians are to DAX. 😊😊
@BIGorilla 3 ай бұрын
Haha, I'm not sure William. But I do like M! Thanks for watching
@djl8710 3 ай бұрын
Awesome!
@rakeshkumarjha6965 3 ай бұрын
Thank you for explaining
@iandelmundo 3 ай бұрын
I'm working with our IT to get Monkey Tools. They asked me if the extension can be published via MS Edge Store or made available for traditional MSI install. Apparently those are the only ones approved.
@skillwavetraining 3 ай бұрын
Hi Ian, we received your support ticket, so will reply directly there. Thanks for your interest in Monkey Tools!
@awahab787898 3 ай бұрын
I was looking for this kind for video for more than two week. Thank you!
@skillwavetraining 3 ай бұрын
Glad it was helpful!
@awahab787898 3 ай бұрын
@@skillwavetraining totally. I saw courses on your site. there must be any kind of suggestions there will help the new learners to understand which course they pick according to their job requirement
@skillwavetraining 3 ай бұрын
@@awahab787898 We do have a Learning Journey page on our site which helps organize the courses by area of interest and skill level, which might help: skillwave.training/learning-journey/
@mrbartuss1 3 ай бұрын
Could you please share the file so we could follow along?
@skillwavetraining 3 ай бұрын
You should be able download a copy of Melissa's sample file here: 1drv.ms/u/s!AuhhLgCXSCKKgYM6pq9BYh1GBnT3Jg?e=nvOak3. We'll also add it to the video description above.
@mrbartuss1 3 ай бұрын
@@skillwavetraining Thank you so much!
@Garycarlyle 3 ай бұрын
Opening another instance that way in Win 11 isnt working for me :(
@skillwavetraining 3 ай бұрын
That's odd... I'm on Win 11, and it is working for me...
@stantonberlinsky5517 3 ай бұрын
Excellent nuance highlighting only the numeric indicator leaving the preface untouched when you add the table, range, or dynamic array name. Great feature for us lazy (or as you say, "efficient") people. Such a great time saver!
@syrophenikan 3 ай бұрын
That’s amazing. I’m finishing a project right now where that will greatly reduce my documentation time, while also making it more thorough. Thanks.
@syrophenikan 3 ай бұрын
The automatic creation of the explicit DAX measures is worth the price of admission!!!
@skillwavetraining 3 ай бұрын
To me, that's just the icing on the cake. The no-effort recreation of everything... that just saves me SOOOO much time!
@Lyriks_ 4 ай бұрын
That's sick ! Go Ken !
@wmcnabb 4 ай бұрын
Ken - can you use format tags in this table?
@skillwavetraining 4 ай бұрын
Interesting question @wmcnabb... This dialog is really a no-code/low-code expression builder for building new DAX tables or modifying existing ones. I think what you're looking for here would be to store the definition of the DAX table in BiblioMonkey so that you could just inject one. I haven't though about this before but in theory it would be something I could add. Do you think you'd find this useful to be able to inject in future workbooks?
@sherryfox227 4 ай бұрын
Exactly what I needed. But while attempting to follow along, I got lost on adding the "Magic Index Column" at 1:18. You said you were doing it, but did not elaborate on how. I tried a few things, but none of them work. Found your page with these directions. Thanks!!!!! I thought I needed to create an INDEX, but it is a Custom column with this formula: =Table.AddIndexColumn([Data],"Row",1,1)
@skillwavetraining 4 ай бұрын
Ah yes, Sorry @sherryfox227. I should have called that out. Glad you figured it out though!
@sherryfox227 4 ай бұрын
@@skillwavetraining I did not see a link to that tutorial page I was at from your site. However you may think about adding that to the video details. With the combination of the video and the site, I was able to do it. It was exactly what I wanted to do. Thanks so very much!
@skillwavetraining 4 ай бұрын
@@sherryfox227 Done, thanks for the suggestion!
@ja3mi2 4 ай бұрын
Love the Monkey!