Lookup Values in the Same Table with Power Query M
Вставка
- Опубліковано 5 сер 2024
- Do you want to filter your dataset on one column but return a value from a different column? This video shows you two approaches how. The second one is my favorite, but a bit trickier!
CONTENTS
00:00 Introduction
00:39 Data
01:09 Method 1
03:05 Method 2
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ua-cam.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#Lookup #powerquery #bigorilla
Thanks Rick - this is great. Your videos are an encyclopedia of "how to" tips. Always the first place I look.
Very useful and nicely explained.
I like the way you gradually build the formula up, adding a layer of complexity at a time. 👍
Thanks, that's exactly my intention, glad it worked! 👏👏
I'd never have thought to have a condition when selecting a column.
More on Records and use of along with generate please, it's fascinating what what you manage to do.
the 2nd method seems more ok to me. Thanks! it was helpful.
Great!
Excellent Content :)
Thank you!
Great video 😊 my default is to merge 2 tables, but I'm always unsure of that is the best way
Nice vid bro! I've learn a lot of Power Query on this channel.
Now, I'm pondering the following:
What are the pros and cons of this columns filled processes, against the "merge queries" process?? Which cases should I use this one or the another one?? Could be one of them the best solution "forever", or like any problem-solving in this query world... it depends? Depends of which general criterias?
I would use the Merge transformation (I also suspect it will be significantly faster as it's not performing the calc on a row-by-row basis).
I did some testing on List.Contains versus Merge for filtering based on a list. Using Merge was 10x faster. I've not done any testing on this scenario, but I suspect it will be the same.
Hey Mark,
What I don’t like about merge is that it takes quite some steps. First merge, then expand. And what would I merge on? Probably I would first have to add a step with the date of today to merge on, then remove the temporary column after, coming to 4 steps.
On the matter if a row by row calculation, one could save the lookup value as a variable, and insert it as the column. No need to perform the calculation again and again.
Performance wise, it’d be great to see some testing. If anyone is eager, I’d love to see the results. 👏
That is amazing! Thanks! But how we do where condition is "Exact match. If none found, return the next larger item"
This is a very good technique but what if i want only set of limited values for example (if i want August month sales value in September), I Know this is possible in DAX but in M Query from a single table?
0:19 I just started atching and I hope itll not be the classical merge but rather inside operator function :)
Simple and very useful, excellent content. Can I ask if this method works with parameters?
Hm help me understand the use case. Where in the solution would you like to implement this?
@@BIGorilla actually its a curiosity, in case needed to change de date dinamically
@@oscarmendez-roca9181 Yes absolutely. I now had the Date of today is input. But you can also reference a date-parameter No problem !
Sir, plz make video for below.
If
1 table has 9 column,
2 table has 10
and 3 table has 8 .
Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.
5:21 huh... great I just learned the easier way, My way in both cases would be more complicated.
sooo thank you
Glad to hear that!
Could you do a lookup value example but when the result column give more that one results not only the first
You could use the Table.SelectRows function powerquery.how/table-selectrows/
What should we do if we have two duplicate days in the date column?
Good question.
1. You can use a different lookup value that is unique
2. You can search on multiple columns that return a unique row
3. You can use method 1 and then return the first row by using the {0}
You can also use Table.SelectRows instead
@@BIGorilla it was very difficult
@@miladarastoueian2874 These are 3 methods, you can pick the one you find easiest :)
@@BIGorilla What if I want to bring a duplicate?
@@miladarastoueian2874 then this is not the right technique. You would probably want to return a list of values, or concatenate them.
Regards
Rick
This is called key match lookup.
Thanks Josh!
@@BIGorilla This works great for small lists, but if you have over 10k rows, then using the merge feature is the fastest way to go which is unfortunate because you have to expand columns, etc.
Was trying this with text values but getting: Expression.SyntaxError: Token Literal expected.
This works fine: = #"Reordered Columns" { [ User_Name0 = "bradp1" ] } [displayName0]
This does not: = #"Reordered Columns" { [ User_Name0 = 'ELT' ] } [displayName0]
There is only 1 row where User_Name0 = "bradp1"
I needed to return the displayName0 from the row where User_Name0 = ELT (in each row of the table)
The table columns:
User_name0, displayName0, ELT
jerrym1, Jerry Matters, bradp1
carryw1, Carry Waters, bradp1
bradp1, Brad Pippen, mikew1
Looking to return the value Brad Pippen for first 2 rows where is matches bradp1
Unfortunately this key match lookup method only works when you get a unique row in the result.
For returning multiple rows you will need to use Table.SelectRows.
For a more detailed view of all the type of errors I can recommend: powerquery.how/error-messages/