Excel Magic Trick 1415: Power Query Lookup and Return Multiple Items And Join Them In Single Cell

Download File:

See how to use Power Query to Lookup and Return Multiple Items And Join Them In Single Cell. This trick comes from Emin Uzun @ YouTube & Bill Szysz @ YouTube. Learn how to Group by Rows and use the Text.Combine Function. Learn how to use the Table.Group Function. Learn about the M Code behind the scenes when you use Power Query.
Topics:
1. (00:15) Introduction to problem and how we think about it in Power Query Using the Grouping Feature.
2. (01:36) User Interface Method by Emin Uzun @ YouTube
3. (05:36) Learn about M Code and Power Query Functions
4. (10:15) Type M Code Method by Bill Szysz @ YouTube

[ExcelInstructor]

Hello Mike,
Thank you Thank you, Thank you Thank you, Thank you Thank you, Thank you Thank you
This solved my problem with ease, and i found solution for my problem with just haalf the video watched! thats how great they are!

[stephanieb]

I knew the excel basics. But was completely unaware of Excels awesomeness.

These videos are great. My manager recently quit, she never gave me the time of day when I told her the reports she gave me was wrong. The reports are still all over the place (I don't have access to pull them directly) but all of these videos helps me make sure my team is contacting the appropriate members.

Thanks for making this so easy to understand

[mattschoular]

An old one but a good one for sure. Just watched it again after searching for a bit. Did the trick. Thanks Mike

[mohamedchakroun]

Thank you Mike for the explanation very nice

[excel]

This is also possible with power pivot. The code for the measure is: = if(hasonevalue(itemtable[item]), values (itemtable[item]), concenatex(values(itemtable[item]), [item], ", "))

[HusseinKorish]

Many thanks for the video and the links for excel files too

[Saur]

Another excellent video. Could you please consider uploading an example of conditional joins in Power Query? I'd love to see an example of matching one table's transactions with another based on Date calculations and lookups (matching transactions by date in Table 02 based on Start and End date columns in Table 01 for example).

[EminUzunN]

"Poet and artist" Bill Szysz :))

[horizon]

Hey, ThanksOne question, Can we do it same process without "Group By" step. I do not want to group the INV together, Can it be done.

[mickaeltaieb]

Thank you for the video.
How do you do the same thing for grouping numbers? When I follow your steps, I get an error message saying "we cannot convert the value 64.5 to type Text." And if when I convert my numbers to Text, I get too many decimals. Any advice?

[deiaalgayyar]

Thanks Mike for all your great Tricks :),
I have something like this but with some duplicates
for example 1001 Quad and again 1001 Quad, So I got for inv. 1001 ( Quad, Quad )
I have to repeat the groping step, how to get just one record of the duplicates ?

[olaayorinde]

Thanks Mike, you are blessings! Please, is there a function like this COUNTIF($C$1:C1, C1) in Power Query and if not, is there any function in power query that can simulate the result of the countif function? What I am trying to achieve is to be able to count duplicate in each row and assign 1 to first occurrence and 2 to second occurrence and so on.