Table Navigation Tricks in Power Query
Vložit
- čas přidán 30. 07. 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, I will share 2 awesome tricks to Navigate within Tables in Power Query!
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:09 Trick 1 - Navigating Using Lookup Operators
4:03 Trick 2 - '?' Question Mark!
6:35 My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall CZcams Channel Below
czcams.com/channels/GQE.html...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses - Věda a technologie
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Hi Bro, I like your all videos and your way of expectation.kindly make a video on two statements. 1- Each keyword and 2- Underscore keyword in M Language.
Chandeep, your videos just keep getting better. Hope you keep sharing as you're an invaluable source on CZcams. The question mark operator is a fun one, just like the coalesce one. Can't say I know many other program languages, but these operators feel special.
Knew about the ? returning a null (also working in "if" expressions) but never tried adding it to both Row number and Column name.
Learned something new!
Thanks for sharing Chandeep!
Absolutely brilliant. Thank you very much!
Glad you like it!
Excellent Golldy
Thanks!
Very well explained! Thank you!
Glad you like it!
Hi Bro, it was good, can you also let know if there is any way to automate the navigation if there an datasource is getting updated then the last table should load
Great video, can i ask what application you use to draw on screen live ?
Thanks a lot. Explained very well understandable :)
Glad it was helpful!
Very useful. Thank you.
Glad it was useful!
Great content. Thanks.
My pleasure!
Great video. I always learn something & you don't waste my time by making the videos longer than necessary. I don't need to watch them @ 1.5x speed like many tutorial creators. Keep up the good work & thanks.
Awesome! Thank you!
Thank you, Secret Boy.
Great video!!
Thanks!
amazing stuff! i haven't watched it yet, but you would be good on the ESPN OCHO Excel challenge (ESPN 2 8/5 5:00 am 8/7 9:00 am 8/7 11:00 pm
Good one. searching for a solution to communicate to the user for any query failure in the form of a pop up (like message box in vba) for example if the data file is moved from a directory , the query file which refers to this data eventually fails on refresh. There may be API calls and yet to check
Good work
Thanks!
Hi sir, I created a table, using summarize function, but I have to work the table from other slicer, using selected values function, but I tried a lot, but couldn't. Please help me in it
Been using Power Query for a few years and never saw the ? trick. Thanks!
Cool!
Hi, great video. I would like to export a ms access query table with power query. The issue I am facing is that when I navigate in the table data, one specific column - in ms access short text - is converted to text automatically and for some reason I am losing dots in records. Is there anyway explicitly specify each column data type or just keep the dots...? (code used : = Source{[Name = "qry_GET_DB_for_Excel"]}[Data] ) Thanks in advance!
I am a beginner in Power BI. Just started working on DAX. I have a question regarding relationship. I have made four tables i.e. Sales, Products, Product Category, Product Sub Category. Found that relatable columns are automatically picked up. ProductKey column in Product table contains unique Product Key and that propagates Sales Table where Product Key gets repeated. The arrow direction is from Products table to Sales table. '1' is added to Products and a '*' is added to Sales. I think this type of relationship is called 'One to Many' but when I check the properties of the relationship it indicates as 'Many to one'. If tried to change to 'One to Many' it shows error statement. Please help me out of this problem. Thanks.
Delete the Relationship and try again by activating, then dragging the column from Primary table to Secondary Table...It should work.
Great video as usual... What if I want to navigate to one column only of the selected table, i.e. I only want column A...
Thanks.
I do it this way:
= Excel.CurrentWorkbook()[[Name]]
@@MelanieBreden thanks for your reply, I mean one column from the expanded table..I will try this as well...
@@abdulrahmanbaamer8624 it works the same way, you have to reference exactly
= Excel.CurrentWorkbook()[Content]{1}[[A]]
@@MelanieBreden worked for appended query, thanks for your collaboration.
I actually want to create a reference query, and get few columns from the original query; I did this: =#"original query name" {[[column1], [column2], [column3]]}.
This returns an expression error: there is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression.
Thanks again
@@abdulrahmanbaamer8624
this returns a list: = #"original query name" [Column1]
and this a table with one Column: = #"original query name" [ [Column1] ]
this get several columns: = #"original query name" [ [Column1], [Column3] ]
This is super...I have one question if you could assist. I have a source file that is downloaded but each time the sheet name changes - say Data15082022 if downloaded on 15/8 and Data16082022 if downloaded on 16/8. This causes query to break each time. How can this be fixed without editing query source each time? Thx
Hi Santosh, You can change your source to the folder, instead of the file itself, sort descending by Creation Date and only keep the top row. This will always give you the latest file.
If you found another solution please share. I would love to know if there is a more efficient way to solve this.
How to navigate table based on value inside any of the table and expand it? Like if any of the column of table x contains ‘Description’ then only expand this table
with a few functions the code would look like this:
= Table.SelectRows(
Table.AddColumn(
Excel.CurrentWorkbook(),
"Check",
each List.Contains(Table.ColumnNames([Content]), "Description")
),
each ([Check] = true)
)[Content]{0}?
I don't get the second part... What is the purpose of putting a "?", as far as navigating goes - besides preventing an error? You are just going to instead force a null value. Where do you go from there?
Thanks.
A big fan of you bro, how should I contact you