Great video! exactly what I was looking for! Better than merging the columns! Thank you very much!

@MyOnlineTrainingHub

2 жыл бұрын

You're welcome Maria.

This is awesome! Saved in my tutorials list to use very soon. Thank you Mynda and Phil!

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear, Celia!

Hi Mynda and Phil. Thanks for this awesome video showing how to achieve a VLOOKUP using the power of list functions! Nice to know another way to do this vs. merging queries. Thanks for sharing :)) Thumbs up!!

@MyOnlineTrainingHub

2 жыл бұрын

Thanks Wayne, glad it was helpful.

Thanks for this awesome video. By using this answer, I have learned also that Power Query from Excel is case sensitive when using column names. Thanks a lot for this!

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful!

Many thanks for this excellent tutorial. The final formulation you show at 4:02 is very closely analogous to the venerable INDEX MATCH formulation for looking up across tables/ranges with a worksheet function.

@MyOnlineTrainingHub

Жыл бұрын

Glad you liked it!

Wow! Jaw dropping awesome! Thank you so much for creating/posting this video.

@MyOnlineTrainingHub

2 жыл бұрын

You're welcome Eric. Glad you enjoyed it.

I used this on a project at work the very next day! This is great

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear 😊

Wow!!! That is a lot easier than doing a merge. Thank You!!!

@MyOnlineTrainingHub

2 жыл бұрын

:) Glad it was helpful Mark

Thanks you both Mynda and Phil👍

@MyOnlineTrainingHub

2 жыл бұрын

Thanks Kebin

Elegant! Thanks, Phil and Mynda!

@MyOnlineTrainingHub

2 жыл бұрын

Thank you!

Nice video and a great tip yet again! thank you Mynda for sharing this! 😊👍

@MyOnlineTrainingHub

2 жыл бұрын

Our pleasure 😊

So easy to follow along! Great vid. Subscribed!

@MyOnlineTrainingHub

11 күн бұрын

Thanks and welcome!

Another great one! Thanks from South Africa.

@MyOnlineTrainingHub

2 жыл бұрын

Cheers Chris.

Hi Mynda!Really Great And Helpful Tutorial From Phil..Thank You Both :)

@MyOnlineTrainingHub

2 жыл бұрын

Thanks Darryl. Glad you liked it.

Thank you, thank you, thank you ... indexes fix nightmares. Dates are a massive pain across tables in Power Query. The might be a way using Min() > than x but I could not work out how to do it after hours and hours. Indexing the reference table +1 to get the next date worked fine. Awesome. Clear as clear.

@MyOnlineTrainingHub

Жыл бұрын

Glad it was helpful 😊

This is valuable knowledge. Thank you for sharing.

@MyOnlineTrainingHub

2 жыл бұрын

My pleasure Adonis.

Mynda, You're quite amazing. Thanks for gathering this information for us.

@MyOnlineTrainingHub

Ай бұрын

You are so welcome! 🙏

Phil... great trick... List.PositionOf very elegant function to bring the ordinal position whitout adding an index number column , I will memorize this list function.

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful Jazzista

A really cool solution! Thank you.

@MyOnlineTrainingHub

2 жыл бұрын

No worries Pavol, glad you liked it.

this is great... i use merges all the time! thank you (my computer thanks you too!!)

@MyOnlineTrainingHub

2 жыл бұрын

Glad you found it helpful 😊

Good use of the 'let' statement to solve a range lookup problem

@MyOnlineTrainingHub

2 жыл бұрын

Cheers, Drew!

This was worth watching, thanks. It might have been worth including doing an error trap if the List.PositionOf returns a -1, before doing a retrieval based on the index value

@MyOnlineTrainingHub

4 ай бұрын

Nice idea. Thanks for sharing, Walter 🙏

Thats really very helpful, i implemented this technique on one of the project i am working in my company.

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful!

This is EXACTLY what i was searching for. Every other answer I could find involved merging tables which is not the same thing

@MyOnlineTrainingHub

2 жыл бұрын

Glad I could help, Tony 😊

Brilliant, exactly what I was looking for.

@MyOnlineTrainingHub

3 ай бұрын

Great to hear!

Hi Mynda and Phil. Thanks for this awesome video. Is it possible to "upgrade" it to two-way lookup ? E.g. if the same Sales Persons are in two different Cities and for Cities are different Rates

Yay just what I wanted thank u!🤩

@MyOnlineTrainingHub

2 жыл бұрын

No worries Taaha.

Super Phil and Mynda...Thank you :)

@MyOnlineTrainingHub

2 жыл бұрын

Our pleasure, Vijay!

Great video!! Is this method better or using combine query = merge better to save the file size/ refresh time?

Awesome, just what I needed.

@MyOnlineTrainingHub

Жыл бұрын

😊 cheers, Jon!

Great Tip! Thanks for sharing :)

@MyOnlineTrainingHub

Жыл бұрын

My pleasure, Vishal!

Thanks Phil& Mynda for the video. As a novice I found it quite difficult to follow because the excel file is the finished product and you jump a couple of stages in setting up the data set with queries which is fine for intermediate and advanced users but I'd have preferred a simpler approach starting with the raw data (I know its probably too basic for many but I'm a novice). Persevering I didn't find the list method particularly intuitive compared to non power query methods in excel so the video is one to refer back to.

@MyOnlineTrainingHub

2 жыл бұрын

Hi James, I understand what you're saying as you are not familiar with PQ, but loading a table into PQ is straightforward and I wouldn't want to go over that in every video I do. All you need to do is create a table in Excel by clicking into the data and then pressing CTRL+T. With the table created, and a cell in the table selected, go to your Ribbon -> Data -> From Table/Range. Or you can right click in the table and select Get data from Table/Range. In the PQ Editor you can then click on the Close & Load drop down (click the black arrow) in the Home tab and choose to Close & Load To .... then choose Connection Only. Regards Phil

Thank you, a useful skill )

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear!

Thank you for taking up this topic Mynda! Can you please also share a video on XLOOKUP in power query!

@MyOnlineTrainingHub

2 жыл бұрын

There's no such thing as XLOOKUP in Power Query as such because Power Query doesn't have Excel Functions. There's lookups using List Functions, which this video covers and there's lookups with Merged tables, which this tutorial covers: www.myonlinetraininghub.com/excel-power-query-vlookup

And this one is effective just awesome.

@MyOnlineTrainingHub

2 жыл бұрын

Great to hear!

Does the exact match list approach perform fast as a merge between data and categories? A merge does the same thing - I like the cleaner code of the list approach but the ease of the UI merge is an advantage - performance is the deciding factor.

@MyOnlineTrainingHub

2 жыл бұрын

It can depend on your data. How many rows? How many unique values? In my tests, using List functions is as fast if not faster than merging. You may need to use Buffer() functions on your lists to get the best performance.

@mmohon

2 жыл бұрын

@@MyOnlineTrainingHub any videos on buffer use?

Wow this is beyond amazing

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful!

Thanks a lot for this.

@MyOnlineTrainingHub

2 жыл бұрын

You're very welcome!

Many thanks. Great help

@MyOnlineTrainingHub

2 ай бұрын

You're welcome! 😊

Thanks Phil and Mynda - great video. What would the syntax be if the Categories table held both Category1 and Category2, and I needed both categories returned? Can your single step be expanded or would I need to essentially duplicate the Add Column? And, if we need a second Add Column (presumably requiring another search through Categories), would this mean that a Merge table would be more efficient?

@scoobystent

2 жыл бұрын

Great question ...... will be interested to see the reply to this one

@MyOnlineTrainingHub

2 жыл бұрын

Hi Dave, If you require 2 values to be returned I don't think you can avoid having 2 separate steps, and hence 2 lookups, to create 2 columns. Because the logic is to add the column, then do the lookup, rather then do the lookup and if you find it then add the column, you have to create the column first, requiring a step to do this. So in such a scenario would a merge be quicker? Maybe. I'd have to test with a representative sample of data. If you used List.Buffer() on your lists (columns) I'd expect that to boost performance. Phil

@davecope3322

2 жыл бұрын

@@MyOnlineTrainingHub Thanks Phil

It is a great solution ❤

@MyOnlineTrainingHub

Жыл бұрын

Glad you found it helpful!

Wow. Great video

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it 😊

This one worth more "Like" button.

@MyOnlineTrainingHub

11 ай бұрын

Glad you think so!

thanks for sharing! do you have a video showing a look up of a particular word from one table to another pls? i.e. ice cream, ice lolly, key word ice, show both of these.

Thank you !. I was looking for a solution to bring data from a Dataverse table into custom column in PowerBI inside a SQL table, both data sources being used in a PowerBI model.

@MyOnlineTrainingHub

6 ай бұрын

Hope it was helpful!

Well Done!

@MyOnlineTrainingHub

2 жыл бұрын

Thx Philip

This was just what I was looking for. I didn't want to create multiple "merge" tables to use when having multiple index/reference tables (e.g. geographic region, category, etc.). I was going to merge "data" table to "index1" table to create "merge1" table, and then "merge 1" table with "index2" table to create "merge2" table, etc. I AM concerned with handing this off to someone else, though, because I don't often encounter users of the M language of power query at work. A short primer should be fine.

@MyOnlineTrainingHub

2 жыл бұрын

Glad it was helpful, Will!

It took me a long time to find this video. I should ask for a video of searching in your channel 🙂I have a table of 46K lines that I need to change some codes in it to their descriptions.

@MyOnlineTrainingHub

Жыл бұрын

Glad you found us eventually 😊

Thanks Phil & Mynda, it is position to use the same method for composite keys ? for e.g. Name and Date, for merge we will just highlight both columns thanks !

@philiptreacy714

Жыл бұрын

Hi Christopher, yes you can do this but you'd need to create a new column that combines the Name and Date into a unique identifier.

Hi Mynda, always wonder how in the add custom column window, you can see the quick view of any formula/available column by simply type in the custom formula field. that would save a lot of time rather than have to type everything manually. thanks in advance

@MyOnlineTrainingHub

2 жыл бұрын

Great suggestion!

I’ve watched this multiple times - thanks so much. QQ - hopefully a simple one, is it possible to pull back more than one column in the add column?….I’ve tried to do this but it messed up so not sure how to work it or even if it’s possible?

@MyOnlineTrainingHub

2 жыл бұрын

Hi Kim, glad the video is helpful. I should think you can add more than one column. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Fantastic !!!!

@MyOnlineTrainingHub

2 жыл бұрын

Glad you like it!

Amazing, dude

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it, Alvaro!

you are my saviour, merging the column in some cases are not efficient

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it 😊

Thanks. I found it takes so long to refresh for a 20K lines data.

@MyOnlineTrainingHub

Жыл бұрын

Hmmm, that's not ideal.

@StevenGoos

Жыл бұрын

Same for me. I have exported the "CRM_Accounts" (29.789 rows) and the "CRM_Contacts" (38.729 rows) tables from MS Dynamics CRM and it takes well over 40 minutes to go through this. Meanwhile the memory utilization by Excel rose up to the 1.2 ~ 1.5 GB range, and the buffer reached already 4.84 GB when processing only 500 rows... I don't know the final numbers as I went away from my computer after 40 minutes to fix myself a drink. This very nice technique seems to be a memory bomb when dealing with larger tables, which is a pity, as I do like the method. Since the column in the All Contacts table contained a large number of null's I already tried to make it a bit more efficient with: = Table.AddColumn(#"Renamed Columns", "Sales Owner", each if [Account Number]=null then "" else CRM_Accounts[Sales Owner]{List.PositionOf(CRM_Accounts[Account Number], [Account Number])}) But I don't think it made any difference... @MyOnlineTrainingHub, any suggestions how to improve the performance or is the technique just not fit for larger (reference) tables?

Excellent 👌

@MyOnlineTrainingHub

2 жыл бұрын

Thanks for watching 😊

Thank you so much for this great video! I applied it into my model it seems to be working but the query is extremely slow now and it takes really long time to refresh and load it. Is there a way to solve this issue? Thank you in advance!

@MyOnlineTrainingHub

2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@yuanzhang9487

2 жыл бұрын

@@MyOnlineTrainingHub I just saw your reply. I will post the question in the link. Thanks!!!

Could this approach be used for big datasets? My PQ model is very complicated and slow because of a lot of merging, it takes >5h to refresh. So I always look for ways to speed it up. And yes, as someone wrote in another comment, it is important what happens when multiple values are found in look up.

@MyOnlineTrainingHub

2 жыл бұрын

Hi Saulius, If you are doing a lookup (exact presumably) you should only get 1 match. I covered a scenario in the video (approximate match) where multiple items are selected. Do you have an example of the type of multiple match you mean? I don't know any reason it wouldn't work on 'big' datasets (how big is big?) Without seeing your data and queries it's hard to give any definitive guidance as to improvements. You may get faster performance by combining List.Buffer() functions to create buffered lists and then using these to do your lookups.

@lopher70

2 жыл бұрын

Hey Saulius, I'm about to create a entire model with 7k rows and 50 cols in the main table, In your experience thiw could be a slow performance model? if does, then I would use other tool rather than PQ What do you think?

@SauliusBaublys

2 жыл бұрын

@@lopher70 It depends on calculations and other tables you are doing. Few tables with 7K would not be very slow model IMHO. Also it depends on your PC. My table has 60K rows and >50 columns, but I merge it with other >50K tables (queries). And I have ~100 queries merged.

This is really good ... PQ has a lot of very interesting and useful function that most of the people don´t know. It is great to have someone who could explain how they work. Do you have any test comparing the performance of two methods?

@MyOnlineTrainingHub

2 жыл бұрын

Thx Gilberto. Yes I have some test data/code but it's in a really ugly file. Let me tidy it up before sharing.

@cajinguy218

Жыл бұрын

@@MyOnlineTrainingHubwas there a large performance gain?

Thanks for this video! I want just ask you a question! How do the functions apear while you taped the code on the box.. it deosn't work in my power query.. Thanks for your Nice job

@MyOnlineTrainingHub

2 жыл бұрын

Hi, It's the Intellisense. Yours must be turned off. In the Power Query editor, go to File -> Options and Setings -> Query Options -> GLOBAL -> Power Query Editor and under Formula (on the right hand side of the window), make sure 'Enable M Intellisense ...' is checked.

Hi, thank you for this video, it was really helpful! I came across -1 values which meant they were not found but PowerQuery has labeled them as "Error." Is there anyway I handle this so that instead of displaying "Error" it should say "Not found"? Please can you direct me to where I can find a solution to this?

@MyOnlineTrainingHub

Жыл бұрын

Insert a step to replace the -1 values with 'Not found'.

Hi Mynda and Philip, excellent video about List.PositionOf. How do you use List functions for multiple column conditions? It's time for me to move away from the slow performing PQ Merge Queries interface (which does allow multiple column conditions).

@philiptreacy714

2 жыл бұрын

Hi, can you give me an example of what you are trying to do ?

@10ozGold

2 жыл бұрын

@@philiptreacy714 Hi Philip, what if the [categories] had all duplicated rows, with an extra column, "Group", either as {"A", "B"}. Then [data] table also had an extra column "Group" {"A", "B"}. Now there are 2 conditions to lookup. Would a List function be possible? If so, this would be a game changer.

brain has not melted 😁excellent

@MyOnlineTrainingHub

Жыл бұрын

That's a good start 😁

Great Video. I have a question in my work area. Is it possible to do the same, but not from a table, but from the end result of another step in the same query? Let me give you an example: I have a table. In the next steps, I perform calculations with the Group By function, filters, etc. In the next step, I recall the basic table again and want to insert the results of the calculations into the related rows of the table in a vlookup-like manner.

@MyOnlineTrainingHub

2 жыл бұрын

Glad you liked it :-) Just duplicate the query, use one for your grouping and the other to bring the grouped data into the original table.

@ssomtom

2 жыл бұрын

@@MyOnlineTrainingHub Thx your answer :) 151 / 5 000 I knew this solution, it just increased the file size due to the duplicate query and interested optimization reasons to see if it could be within a query.

Hi, Very good. List.PositionOf function is like Match in excel? Correct?

@MyOnlineTrainingHub

2 жыл бұрын

Hi Dino, List.PositionOf works in a similar way but doesn't require numeric values to be sorted like MATCH does when looking for largest or smallest values..

hello, i am new in power query. i need to use this strategy. but the thing is, my table (TABLE1) has used power query already loaded in 1 worksheet. on same workbook, I have added another table (TABLE2) in new worksheet forwhich one of its column need to match data and the get data to be added as another column to TABLE1. Ex. Table 1 with student name and grade needs to match with table 2 with name and sections. Result shall be in Table 1 with student name, grade, section as columns. I hope you can answer this. thanks much in advance.

@MyOnlineTrainingHub

10 ай бұрын

Please post your question on our Excel forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

Would this improve performance? I have had issues using merge when the 2 tables being merged are one to one (the same record is on both tables but one of the tables has additional info which I need to incorporate). Both tables are about 500 K records big and when I try to merge them, PQ slows to a crawl.

@MyOnlineTrainingHub

2 жыл бұрын

Hi Giancarlo, quite possibly with some tweaks. Can you post your data on our forum and I'll take a look at it. Phil

@kedarhatkar

Жыл бұрын

@@MyOnlineTrainingHub I am new to power query wondering why not use merge for the first part to get the category, could you please explain the pros of using this method and cons of using merge, thanks in advance really leran a lot from you videos 😊

Hi Munda and Phile. Thanks for the great content. I have a query, please help. What if the lookup table has two columns one is for range and the other one is for sales (a calculated column). I want to rank the sales basis the range using approximate match in another table. Please help it's urgent 🥺

@MyOnlineTrainingHub

2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Thank you for this detailed video on how to replace vlookup using Power Query. Is it possible to make this process into a query function?

@MyOnlineTrainingHub

Жыл бұрын

Yes you can!

@enricomendiola9952

Жыл бұрын

Thank you 😊

Is merging makes the query more slow than doing lookup using list?

That is amazing! Thanks! But how we do where condition is "Exact match. If none found, return the next larger item". In excel we use xlookup but how we do in power query?

@MyOnlineTrainingHub

7 ай бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@advrohitowhal9794

7 ай бұрын

Thanks. Hi after registrations, unable to login to post the question.

@advrohitowhal9794

7 ай бұрын

@@MyOnlineTrainingHub Query is Post this forum. thanks.

Hi. How do you activate the "formula/function assist" to show available functions/tables as he was typing it?

@MyOnlineTrainingHub

2 жыл бұрын

It’s available in later versions of Excel. You’d need to upgrade to get it.

How do you enable the hint bar when typing out the formulas in the custom column?

@MyOnlineTrainingHub

2 жыл бұрын

You either have it or you don't. If you don't have it then you'd have to upgrade to a later version of Excel. I'm not sure what version it came out in, but it's definitely available with a Microsoft 365 license.

How can this be used for just filling the blanks in column using reference og any of the column like a lookup... Just for blank values only

@MyOnlineTrainingHub

Ай бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

Great video, especially the combining of several steps into one, this often gets glossed over. I'm guessing that the approximate match with the assigned variable couldn't be combined in a single step?

@MyOnlineTrainingHub

2 жыл бұрын

Thx Rico. Yes those steps could be combined (maybe I should have shown that in the video!) You'd need just 1 step - create a Custom Column with this code BonusRates[Rate]{List.Count( let val = [Sales] in List.Select( BonusRates[Threshold] , each _

I have a list of employees and each has individual rates standard overtime rates which the timecards. Tell the rate to use looking for a simply way to return the rates. Any examples to look at please.

@MyOnlineTrainingHub

Ай бұрын

Lookups sound like the way to go. If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

Helpful

@MyOnlineTrainingHub

9 ай бұрын

Glad to hear that 😊

hey mynda the list.position function is taking more time thatn it would take in mergin the query is it supposed to be like that or am i doing something wrong could you please let me know?

@MyOnlineTrainingHub

2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Hello!!! Greteengs from Colombia. I have a question, how can i do the same vlookup but conditional? For example, if condittion1 is ok then put in the cell "Ok" else go to another table and to do the same vlookup for to do another search.. Thankssss

@MyOnlineTrainingHub

2 жыл бұрын

Hi Sergio, please see this tutorial: www.myonlinetraininghub.com/power-query-if-statements

the logic workes but it subtantially increases the query load time to a point were if the data set is big then the query almost always times out, is there a work around for it.

@PhilipTreacy0

Жыл бұрын

Try using the List.Buffer function to store the list(s) in memory and you should see a speed improvement.

Great tutorial... I'm sure I'm making a rookie error, but I have two tables [Product] and [Seasonal_Lookup]. Like you, I made Seasonal_Lookup connection only. Seasonal_Lookup has two Columns and I created the command " = Table.AddColumn(#"Add CL1 Margin", "Custom", each List.PositionOf(Seasonal_Lookup[Collection_Tag],[Title])) " . The one thing I have noticed and am getting stuck on is ... When I Enter the Power Query in the editor, it finds " Seasonal_Lookup " as a value I can insert, but, it can't seem to find the Column " [Collection_Tag] " within the table. I have a second Column names "Season" and I've tried that too. Is there some trick to having Power Query find the Column Header by name? it definitely shows as a column Header, but I've tried everything to try to address it and can't seem to. thanks

@MyOnlineTrainingHub

2 жыл бұрын

It's difficult to picture your problem, however it's important to understand that Power Query is case sensitive, so it will be looking for an exact match on any reference to column names. If you're still stuck please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

hi how we can relations between many to many ? for example table of customers sales and table payment customers table ? thanks

@MyOnlineTrainingHub

2 жыл бұрын

Many to many is only possible in Power BI. I would set up a dimension table for your customers and create a many to one relationship between the customers sales and payment tables to the customers dimension table.

@AnwarAlfaqeehAlhusseini

2 жыл бұрын

@@MyOnlineTrainingHub thanks 🙏 I appreciate your help

Greeting madam, I tray to follow all of your vedio but due to enough time I can't follow all vedio. I have a request to you to make a vedio on material management specially in mechanical section.

@MyOnlineTrainingHub

2 жыл бұрын

Thanks for the suggestion :-)

Is this method work faster than merge step??

Will it work on large datasets?

@kumshan1407

2 жыл бұрын

Is this an optimized solution for looking up value on large datasets

@MyOnlineTrainingHub

2 жыл бұрын

Hi Kumar, I don't know any reason it wouldn't work on 'large' datasets (how large is large?) I haven't optimised this in any way. I'd expect you'd get better performance by using the List.Buffer() function to create buffered lists that can then be fed into the List functions doing the lookups.

My vlookup doesn't work on the values extracted through mid formula. when i copy-paste these values, means removing the mid formula then vlookup does work. i change the format to numbers as well but didn't get fix the issue. Can you help me, please

@MyOnlineTrainingHub

2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Hello man it is possible to get slope results in power query

@MyOnlineTrainingHub

2 жыл бұрын

Probably, but I haven't tried it before.

Thanks for the tutorial but it doesn't work with direct query

Beautyfull

@MyOnlineTrainingHub

2 жыл бұрын

Thank you Luciano

i tried this approach....though it reduces the number of applied steps, it takes a lot of time when i refresh the preview.....thats a major problem ..... merge happens in seconds.....am i missing anything here. any suggestions ??

@philiptreacy714

2 жыл бұрын

Hi, Try wrapping your lists in List.Buffer

how to do this if we need to look for more than one column?

@MyOnlineTrainingHub

3 ай бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

what's the benefit of this compared to merging queries?

@pascaljoly5752

5 ай бұрын

I am still fairly new at power query but i do use merge queries quite a bit in my work and i find it really easy to use where i'd have to watch this tutorial several times to get it. but if there is a real beneft, i shall do it

How do I compute Fifo method in power query

@MyOnlineTrainingHub

Жыл бұрын

I don't have any examples I can point you to for FIFO.

How about using merge instead of list function?

@MyOnlineTrainingHub

2 жыл бұрын

You can use the table merge approach if you wish as explained in these posts www.myonlinetraininghub.com/excel-power-query-vlookup www.myonlinetraininghub.com/power-query-approximate-match-vlookup

This is not working so great for me. I have 10.000 rows of data and it take several minutes to complete the calculation. I guess i stick to joining the tables.

@MyOnlineTrainingHub

9 ай бұрын

That's a shame. I agree, do what works best for your scenario.

This only works on a 1 to 1 right? 1 to Many does not work from what I can see.

@MyOnlineTrainingHub

2 жыл бұрын

Hi Dayve, not quite sure what you mean. If you're doing a lookup, you'll only be looking up in 1 table? If you need to lookup the same value in multiple tables then you'll do a lookup for each table. Or consolidate lookup tables into 1 table and then do a single lookup returning multiple columns.

When I search for the column on the other table, it doesn't find the columns. What am I doing wrong ?

@MyOnlineTrainingHub

Жыл бұрын

Hard to say, Matheus. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

I am not understanding the benefit of using power query in this example. If we use vlookup(), it's much quicker to find the answers.

@MyOnlineTrainingHub

Жыл бұрын

With Power Query you can bring data into Excel from multiple sources, perform the lookup and then load just one table into Excel with no formulas. This is far more efficient than having a load of excess data and thousands of formulas in your file. Hope that helps clarify.

Where is attachment ?

@MyOnlineTrainingHub

2 жыл бұрын

Hi Sandeep, click SHOW MORE under the video and there's a link to it.

too fast for me :P bur.....I try

@MyOnlineTrainingHub

8 ай бұрын

Reach out if you get stuck: www.myonlinetraininghub.com/excel-forum