Concatenate, Merge or Combine multiple rows into one value - Power Query for Power BI

hello everyone and welcome to this new video on how to concatenate multiple rows of theta integers one single value in this case I'm gonna be using the power bi desktop instead of the excel and pretty much we're gonna be using power Corey is gonna be the same process here in power bi as well as in the Excel version of power yeah power query so let's go ahead I'm gonna get the data it's actually in an excel file so I'm just gonna click get data from excel your data can be anywhere it can be on any any data source to be honest in my case it's just gonna be on this one I believe it's gonna be on music sales you're gonna be able to download this workbook as well so just check the link in the description below you're gonna be able to download this and follow along so sales is the name of the workbook and then I have this table right here calls the sales table I'm going to click on edit instead of load because I want to do something so first thing that I notice that I have four fields or four columns the first one is sales order salesperson country and amount what I want to accomplish is basically get every salesperson so a distinct values for salesperson and then the top three countries for that specific person but before I do that I need to actually make some aggregations I want to get the total numbers for the combination of the salesperson and the country so we can see what was the actual total amount because right now we actually get it at the sales order level so I'm gonna go ahead and right here on the Home tab I'm gonna go on goodbye you can actually choose this group by feature from the transform tab so it's the same icon and now I'm gonna have to group I on the Advanced section cells over there by salesperson ad grouping by country there we go and now all I need to do is just a sum of the amount column just like that so I'm gonna get three fields salesperson is gonna be grouped by salesperson and country and I'm gonna get what was the total amount for that combination just like here there we go so now instead of having over 999 rows as we can see here down below now we only get eighty rules with three columns so that's nice so we have three columns Eddie rows and this is so basically unique values for the combination of salesperson and country now we need to find a way to just summarize by the salesperson column so we're gonna be doing another group by now before I do that I'm gonna do a soaring right here so sorting you can do sorting in two different ways and just gonna delete this one you can do it just on these sales or you can get really creative and you can go sort ascending and then sort by descending on sales and you're gonna notice that there's a number right next to the actual field name one and then number two and that is the ordering of the sorting so I have that is being sorted first by this salesperson so I see Alberto all the way from 1 to 10 and then have cells is the second field that is being sorted and I see that US system number one for a realtor for Bill is Japan for Ken is actually Panama for Marco is Japan and so forth and so forth if I actually go and scroll down we can see the rest of the salesperson so now I'm gonna do a a group i it's just gonna be by the salesperson and now what i want to do is instead of actually doing any aggregations I'm gonna do an operation called the olive rolls I'm just gonna name this my table just like this and what's this gonna give me is the salesperson and then at table as we can see here now I'm gonna be using one line of code so I'm gonna create a new call I'm gonna go to add column custom column and this is the line of code I'm gonna use the table column just like this and what the what table that column does is that it basically extracts I come from a table and transforms me transforms that column into a list so it needs the first parameter to be a table all of my table values are inside the my table column and the name of the comb that I want to extract this call country so just like this so table dot column my table country there we go and I get this list really nice so I'm gonna remove this one because I really don't need it and something that actually came with the January version of the power bi desktop of power query for power bi desktop and that is now available in the Excel version of power query is that you can actually either expand to new rows which is something that we are used to with just simply expand everything or now we have the second option that is called the extract values so you do extract values you're gonna notice that we have the option to select a delimiter so I'm gonna use the coma and then space and then hit OK as we see here and now I have basically a list or basically all the that countries they're basically on the Orion that I used on this sorted rows so for our Bertha US Japan China sorry US China Spain for bills Japan US Italy can expand around Italy UK Japan Canada Australia and so for now here comes the the really advanced bar if if you want to do this it's completely optional but let's just say that from example from this list I can actually just click on this list and inside of a list you can have this one this option that says keep top items so I just want to keep the top three and on the formula bar you're gonna notice what is the formula that is being used so I'm just gonna grab it and I notice what is the pad the actual parameters that is using it's just the list and then how many top rows you want to use so I'm gonna use it against this list that I have here so another custom column because I only want to have the top three so that is the list then number three and now instead of getting all of the countries I only get the top three as shown here and now I can do the same just extract values there we go go to custom comma and a space hit OK and there you have it now you have the top countries as a new column and it soldiers in one single row you can do this pattern with pretty much anything it can be text it can be numeric it can be dates it's completely up to you you can do anything that you want to be honest this is just a single pattern that can be used throughout your whole experience with power query and that's it for the video thank you

Channel: The Power User

Views: 170,300

Rating: 4.8652062 out of 5

Keywords: power query, power bi, microsoft, business intelligence, data, analytics, excel, m language, concatenate, combine, merge, values, list.topn, table.column, mashup, etl, tips, tricks

Id: nJ7LzwiSwnw

Length: 8min 41sec (521 seconds)

Published: Sun Jun 04 2017