In merging the queries, I realized it messed up the order of the transaction codes.How can you ensure the merging does not change the arrangement ,especially if the corresponding value is “null”
Thank you so much for tutorial.. I want to ask about, How First table vlookup to second table if second table have two or more data. For excemple, first table have field id customer "001", and the second table have field id customer "001" and second field Job "Docter" and "teacher". IF second table have 2 of Job for id costumer "001", power query will take data wich one?
So if you want only one value then you need to remove duplicate after join, it will remove second entry if any.and for multiple value the normal procedure which is shown in video.
I want to do the vlookup in my target table not. just looking at the source data, but with additional condittion tthat thte date condition falls between 2 dates in the looked up tablee
Is it possible to use the merge in Power Query to do a vlook up with two criteria? I've done them using a helper column in the look up table and writing A1&"l"&B1 in the value part for the vlook up formula. Thanks!
By 2 criteria if you mean concatenated Vlookup, it is possible. While merging the query just select the columns in the same order for both the tables and then apply "Left Outer Join". Hope that helps
Hi Thank you, I have quistion. My data file / secondary file contains 64785 lines of data and I am only interested in the match, who or what can i do here? When i follow this eks. I do get the matches but also all other items numbers.
This is really awesome, but how to use partial Vlookup in power query... Let's say, in a cell it has full name 'Ranjith Singh' and I need to Vlookup in other table which only has 'Ranjith' and need to find his salary. Pls help how to run this in power query
why do i get the following error "DataFormat.Error: We couldn't convert to Number. Details: 176.1.CAP" Both Columns referenced were formatted as numbers "1.2 type" to return a text value field. I have tried to format both number fields as a text to pull and still no luck. Can anyone explain?
@@GoodlyChandeep thank you for the quick reply I am having a problem in removing duplicates and keeping only the latest record. When I am trying to remove the duplicates it removing as per the order in source data and not per sorting done.
Hi Guys, anyone know how to return a different value if it finds null ie no match , if you do merge and it returns null in power query how do you tell it to return a different column
2 Ways - You can write another conditional column. - or if you know how to write M Language, in the formula bar wrap the merge step in an IF statement.
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
I understand thoroughly, it is precise and to the dart. Perfect !!!
Perfect, exactly what I was looking for. Thank you!
Thank you, easy and clear instructions.
Short and to the point, great job. thanks!
Nice and to the point. Now i know how to do vlookup in power query. Thanks!
Thanks. Exactly what I was looking for!
Glad you found it helpful.!
Great post
Thanks Pratik, glad you liked it
In merging the queries, I realized it messed up the order of the transaction codes.How can you ensure the merging does not change the arrangement ,especially if the corresponding value is “null”
Thank you so much for tutorial.. I want to ask about, How First table vlookup to second table if second table have two or more data. For excemple, first table have field id customer "001", and the second table have field id customer "001" and second field Job "Docter" and "teacher". IF second table have 2 of Job for id costumer "001", power query will take data wich one?
Hi power query will take both records!
So if you want only one value then you need to remove duplicate after join, it will remove second entry if any.and for multiple value the normal procedure which is shown in video.
@@asimkumarchakraborty2456 remove duplicates before the join
@GoodlyChandeep
How is ur power query so fast
Mine is taking ages for each step to load
Very nice and to the point. Amazing!
I want to do the vlookup in my target table not. just looking at the source data, but with additional condittion tthat thte date condition falls between 2 dates in the looked up tablee
Is possible to use power query instead of vlookup in case if the data table has repeated values?
Is it possible to use the merge in Power Query to do a vlook up with two criteria? I've done them using a helper column in the look up table and writing A1&"l"&B1 in the value part for the vlook up formula. Thanks!
By 2 criteria if you mean concatenated Vlookup, it is possible. While merging the query just select the columns in the same order for both the tables and then apply "Left Outer Join".
Hope that helps
Cool man... Doing great stuff..
Thanks!
Thank you. Very helpful.
Glad you liked it
Good job dud 👍👍👍
Really appreciate.. Thank you so much for this video.
Glad you liked it!
how to save the query and resultant excel sheet as in the same file, I did with csv format, previous contents ,queries gone, what is the solution?
Data search in power query but not give out put, pls help us error show in no more 999+ row
Helpful..thanks 👍
Thank you so much.
Hi Thank you, I have quistion. My data file / secondary file contains 64785 lines of data and I am only interested in the match, who or what can i do here? When i follow this eks. I do get the matches but also all other items numbers.
nicely explained. thanks
Glad it was helpful!
This is really awesome, but how to use partial Vlookup in power query... Let's say, in a cell it has full name 'Ranjith Singh' and I need to Vlookup in other table which only has 'Ranjith' and need to find his salary. Pls help how to run this in power query
You can split first-name, middle name and last name by blank, then left outer join and again merge the column by space, simple...
I have 20lacs of data in excel firstly how should I import in power query and how can I do vlookup in two large files in vlookup
Dont do a vlookup. Create a relationship between 2 tables
I think it can lookup first value similar to vlookup and what about for multiple lookup values of same transaction codes
Yes it would work on duplicates too. That means that if the transaction code is duplicated, you'd receive 2 values
@@GoodlyChandeep thanks for your info.i had a doubt because vlookup it always give first search value.
@@raghuv7114 PowerQuery Vlookup has the ability to deliver multiple values found!
Cheers
why do i get the following error "DataFormat.Error: We couldn't convert to Number. Details: 176.1.CAP" Both Columns referenced were formatted as numbers "1.2 type" to return a text value field. I have tried to format both number fields as a text to pull and still no luck. Can anyone explain?
Can this be used in case of a excel file having more than 10-15 different vlookups from other excel file?
Yes but I'd recommend that you make use of Power Pivot - Relationships
Hello Chandeep is it possible to pick up the second value or the nth value
The good thing about Power Query is that it will fetch all duplicates found. Nothing changes in the process. It's the same
@@GoodlyChandeep thank you for the quick reply I am having a problem in removing duplicates and keeping only the latest record. When I am trying to remove the duplicates it removing as per the order in source data and not per sorting done.
Hi Guys,
anyone know how to return a different value if it finds null ie no match , if you do merge and it returns null in power query how do you tell it to return a different column
2 Ways
- You can write another conditional column.
- or if you know how to write M Language, in the formula bar wrap the merge step in an IF statement.