Welcome to my KZread channel dedicated to helping Business Analysts and Data Analysts upskill and thrive in their careers. My videos provide comprehensive and easy-to-follow tutorials on a range of data tools and techniques that are essential for success in these roles.
If you're looking to learn how to use specific data tools such as Power Query, Power BI, DAX, Python libraries, and ETL tools, you've come to the right place. Our step-by-step tutorials will guide you through each tool's functionalities and show you how to apply them to real-world data challenges.
Our goal is to empower Business Analysts and Data Analysts to excel in their work and stay ahead of the curve in the rapidly evolving data landscape. So, whether you're a seasoned pro or just starting out in this field, our channel has something for everyone.
Don't forget to subscribe to our channel and hit the notification bell to stay up-to-date with our latest videos. Thanks for watching
Пікірлер
THANK YOU!! Your video is the ONLY one that solves this aggravating issue!!
I got error when setting sorting each level. I created new column of Index for each level. Error showed about more than one value relation. Do you know how to fix it?
Awesome tutorial and explanation! This underscore trick is so cool to make your M-code cleaner.
Excellent solution. The List.Accumulate function is not very easy, but is important to know it. Thank for sharing
Excellent! Most solutions out there start with a merge query which might not be applicable when there is no common column between the two tables.
This is fantastic, thanks a lot man
Thank you! I appreciate the sophisticated example.
Am I correct in saying that this creates a record and then references that record?
💖@1:07 "I love Power Query, by the way." Me, too! And your videos are helping me love it even more! Thank you!
This is sooo good! Thank you! 🙏
Brilliant!
This is gold. Not only does it solve the issue itself, but taught me a simple approach to do a nested 'let... in'. This will come in handy for a lot of different issues!!! Thank you very much!
Hi Sensai, could you please let me know whether this is possible in pq I have two tables in powerquery One table -Custreview ProductId, customer-comments 100, it sucks 102, this is amazing Second table -custmoderate ProductId, lookupword, replaceword 100, sucks, not good 100,it, this 102,amazing,exemplary From Custreview table match Product Id with custmoderate table; if matched; take customer comments field from Custreview and replace lookupword with replaceword. One Product Id can have 100's of word that need to be replaced. Please guide me how to do it in powerquery.
Thank you dude. Your tutorials always rock.
How can you create a library of custom functions?
bro is a hero way better than whatever they're doing in stackoverflow
So simple, so fast! You are AMAZING!
Plz zoom in the screen in next videos
Can i use this to draw a performance chart
Hey James, I've got a bunch of .pspc files from AutoCAD Plant3D. I can open them in “SQLite Expert Personal 5 - 64bit” and see a list of tables, but I specifically need data from the “EngineeringItems” table in each spec file. I'm attempting to use Power Query to link to a folder containing these files, but when I try to expand the binaries, it doesn't recognize the .pspc format. I've tried searching ChatGPT and KZread for solutions, but I'm just getting more confused. Do you happen to know any magical way to connect to an unknown file format? 😊
Great deal man! do you have other content where you can show who are actually the lost custumers. Their names! so you can track efforts with de sales team.
brilliant!
I love these non pivot solutions to pivoting.
That is brilliant, and I'm still working on understanding how it all fits together, but I did have a go myself, this did mean having a unique date or index column, then Table.SelectRows( <= current index, then take the values as a list and List. transform those that meet condition to 1 or else 0, then sum , not quite as good at does repeat if the previous value if the condition isn't met. Now off to watch 'column groups to rows' . 😊😊
You are just amazing man, concepts and solutions you bring are out of the box...
Another great video, would like to hear the real-world situation that prompted this. Seems like a strange thing to need to accomplish.
So in a record you squeeze 2 integers, 1 list and 1 table... Nice! I want you do it (same outcome) with nested LET, to really make a point.
Looks interesting but I'm going to have to work through that quite a few times to understand what is happening in each step.
amazing video
Thank U very very much!!!
Man! You are really Cool! Thanks a lot!!!
Great video! Thanks for all your content! I've been looking for a way to pull in a value from the 'outer table' into my nested table for hours. All examples seem to have to add a new column to the outer table first. Is there a way to add the "PortfolioAndSource" column in your example directly to the nested table? - Best.
You are awesome
Brilliant! Thanks James ❤👍
Great as usual but I'll have to go through it a couple of times to work it out!
The "meta" trick is very useful in some situations, but it should come with a big warning that if you add steps to the end of the query containing the "meta" statement, you have to manually cut/paste that part to the end of whatever the new "last step" is. Otherwise, any queries that depend on that meta step will break. Unfortunately, it isn't one of those persistent things that automatically updates the M code when you add more steps 😒
That's the reason why I prefer the record-replacement trick instead of using hardcoded meta step-value . Just replace the LET and the IN part with square brackets and voilà... your query keeps functional and you can reference each step you want from outside.
@@adrianoschwenkberg6773 Ohhhhhh, that's the thing where you convert the whole query into one big record, right? I saw someone do that, and I was like, "Well, that's interesting, but I don't know what I would do with it." Thanks!!
@larmondoflairallen4705 for example as a container for your personal custom function collection...
This is a great video but there is one thing that is not quite right. You say that Text.TrimStart(_, {",","."}) removes ALL the commas and periods but that not true. This function only removes the leading commas and periods. Any trailing commas and periods remain. Trailing commas and periods can be removed with Text.TrimEnd. To remove all commas and periods in one go, it is probably best to use Text.Remove or Text.Trim.
I have a card in Power BI showing the last value of a column in a date format. How can I make the background colour change if the date displayed is older than two days from today. I have clicked the FX button next to the background colour in the card formatting section, but it only allows some conditional formatting with a text option, no formula option. Thanks
You have to develop a measure that checks the condition and returns the hex color code that you want. E.g. if condition met then "#4B55W4" (here you place the color code you want)
Excellent as always👍. Thanks James❤
nice workflow, thank you. may I give you an alternative way for this common typo-problem here kzread.info/dash/bejne/dImV3MqaY7acgtI.htmlsi=mgUNjhxXRUT5fo7Z&t=129 After autocomplete the functionname by pressing tab-key, then hold shift-key + Down-Arrow. Now you can type the open-bracket and the whole expression becomes wrapped arround the brackets and you dont even need to close it. Second note, i prefer to type functionnames by starting with the period-key. For example .Combine instead of Table.Combine. In that case you have less typing and the autocomplete dont mess with double-ing the text TableTable.Combine.
That is a handy tip. It will be a grand and glorious day when Microsoft finally fixes that infuriating "feature" that everyone complains about.
1:29 => If we had used Excel.Workbook(_, true) instead, we wouldn't have needed the next step.
You're a Meta Wizard 🧙🏽 🤟🏽
Man! You are a freak of PQ. Thanks dude.
Cool !!!
The meta magic is incredible! Just what I have been looking for. Thanks
Great tips!
Excelent solution. Thank for share
Damm i was trying to do what 4 hours until I found your video. Thx.
Awesome ✨
Hello I am trying to use power query to join/merge two table based on a sequence number string. For example I have two columns Ticket Number contains a 14 character string 00106480803209 and a Revised Ticket Number which has only 10 characters 648080320. The 10 characters are the same sequence by dropped the first 4 and last character. I would use extract range but the data is not consistent, just the sequence of 10 characters. Can Power Query join these data sets?