How To Pick-Up Cell Value Using Power Query - Power BI & Excel Trick
HTML-код
- Опубликовано: 1 авг 2024
- Hey, data wizards! 🧙♂️
Welcome back to the channel! In today's video, we're unleashing the magic of Power Query with a super cool trick💥🔮
Tired of manual data entry and copy-pasting cell values between tables? Yeah, I feel you! But fear not, because Power Query is here to save the day! 🦸♂️💪
I'll walk you through this nifty technique step by step, making it super easy to follow along, even if you're new to the world of data manipulation.
Power Query is like a secret weapon for data enthusiasts like us. It lets you clean, transform, and reshape data with ease!
In the video, we'll dive right into a practical example where you need to extract specific cell values from one table and convert them into a column. Power Query has got your back! 💰📈
By the end of this tutorial, you'll be a Power Query maestro, effortlessly picking up cell values like a pro! So, hit that play button, and let's get started on this data adventure together! 🎥🚀
I hope you enjoy this one!
Have a good one,
Roland
💪 New videos coming every week 🤹♂
🤘 New short videos coming every week 👨🏫
🔗MS Learn Record Functions:
learn.microsoft.com/en-us/pow...
🎬 MORE VIDEOS:
Power BI & Power Automate - How to automate reporting process -
• Power BI & Power Autom...
Get data from folder 🔨 / Combine multiple Excel files 🔧 - • Get data from folder 🔨...
Dynamic Row-level Security 🔐 - Based on Dimension Tables - • Dynamic Row-level Secu...
Chapters:
00:00 Topic Of The Day - Pick-up a Cell in Power Query
00:38 Intro
00:49 BI-Lingual Analytics
01:08 Excel Invoice - Pivot Table Format
01:50 Dummy Invoices / Files
02:21 Import Files From a Folder
02:40 Manual Fix - Copying dates
03:09 Pick-up Cell Value Automatically
06:30 Final Result - Cost Trend Analysis
06:49 What Data Can Be Converted?
07:21 Date Table (!!!)
07:48 Questions? Comments?
08:00 End
▼▼▼▼▼▼▼▼▼
Make sure to hit the 👍 button and ❗❗ SUBSCRIBE ❗❗ to my channel.
If you have any questions, just let me know either in the comments down below 👇 or send us an e-mail 📩.
▲▲▲▲▲▲▲▲▲
🤝 HOW TO CONNECT 🤝
LinkedIn - / bilingualanalytics
Twitter - / bilanalytics
Subscribe - / @bilingualanalytics
ABOUT ME: bilingualanalytics.com.au/abo...
📍 - Sydney, Australia
📽 GEAR:
📷 - Sony ZV-E10
🎙 - Audio Technica AT2020
🔦 - Neewer 18” Ring Light, Elgato Key Light
🎧 - BOSE QC35
🖥 - ASUS VC279H + Xiaomi Mi Curved 34"
💻 - Intel i7-12700K, Gigabyte Aorus Master 3080 12GB, Corsair Vengeance LPX 64GB, Samsung 980Pro 1TB
#PowerBI #PowerQuery #Excel 
Наука
Have you used this trick before? Is it new to you? Let me know here 👇👇👇
Thanks, exactly what I was looking for!
Happy to hear that!
This is such an elegant way of solving a common problem. Your outstanding, clear, concise video enables me to eliminate a lot of inefficient, ugly, or manual workarounds. Much appreciated!
Thanks for the kind words! Glad you liked it :)
THank you, very helpful, this was what I was looking for
Happy to hear that! :)
Just saved my life thanks legend
Happy to hear that! :)
Defiantly used it before, neat trick to use for a dynamic File or Folder Path.
Absolutely! :)
Great video. Did you post the link to the "powerful powerbi record functions" mentioned in 4:15?
Glad you liked it!
Yes, its in the video description, but here is the link:
learn.microsoft.com/en-us/powerquery-m/record-functions?WT.mc_id=DP-MVP-5005234
Hello Roland, am new to power query and found your video very useful. However, I was not able to use this trick because the 'Getdate' intellisense was not available on my laptop. Any ideas on how I can fully benefit from this trick or how I can make the 'Getdate' function in power query on my PC work?
hey there, try typing it in instead of relying on the pre-populated stuff. sometimes it gives me headaches as well :)
is there a way of doing the same thing as a measure? really great video, will be using this solution
Thanks! Could you please let me know how (and why) would you like to use a single value as a measure? I mean if that's what you want to use, just import that single cell and that's it, but I have a feeling there might be something else behind your question :)
Trying to use this trick while applying it to multiple nested tables at a time, but it doesn't seem to be accurately updating the variable (or is using the latest value of it). I haven't used variables much in PowerBI/Power Query so I'm not sure how it handles them... but I'm assuming that's where it is breaking down since everything else works
It should still work. Maybe set up a helper query first and then apply the steps from that to all queries.
@@BILingualAnalytics That helped me troubleshoot the issue, thanks!
@@SatisfiedOnion happy to hear that! That's usually my go to solution with nested queries. It helps with troubleshooting as well.
I used this to get the date from a cell, worked great, but now how do I use it to add another custom column which gets the contents of another cell in the same column
Once you get that single value out you should be able to reference anywhere in Power Query.
It picks the date from the first file and uses it for every other file in the folder.
How do I rectify this
Have you replicated this method on the sample file or on the combined query?
My bad