![]() Hi, Ken - I just wanted to take a moment and tell you that this solution was AMAZINGLY simple and I just used it last night on a project to great success. RemovedColumns = Table.ReorderColumns(AddedSortIndex,List.Combine(, "Index") Let AddedSortIndex = Table.AddIndexColumn(table, text, 1, 1), Suoyin = (table as table, text as text) as table => The below code needs to be added into a blank query in the advanced editor: The english from the code is not great, but the code works fantastically. I have been using the following for this - I call it a "clustered index". It's called "Advanced Row Context" (in our M deep dive section) where Miguel shows all kinds of cool stuff that you can do by adding new columns to Grouped Rows. I would also be remiss if I didn't mention that we have a great video in our Power Query Academy that covers this kind of operation (among others). If you want to play with this one, the example file can be found here. Which leaves us with our table in place including the new column which does number rows by Group as originally planned. Expand all columns from the Partitioned table except the Group (since we already have it).Then I added my custom column, pasted the code, and replaced #"Grouped Rows" (the name of the previous step) with the column I wanted to use. I copied that code, and deleted the step. =Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1) That added the following code in the formula bar: Where did I get this code? I actually added an Index column to the whole table. To do this, I added a custom column using the following code: The secret here is to add an Index column to each of the individual tables in the Data column, then expand the columns. But if we expand the column, then the index will not reset when it hits Food, and the numbering won't be right either. ![]() If we try to add it here, we'll get 2 new values, showing 1 for Alcohol and 2 for Food. In order to number rows by group, what we really want to do is add an Index column, but we need to add it for each of the individual tables in our grouped segments. Add a aggregation column for "All Rows".Sort the data based on the Sales column (in descending order).Assuming we start by pulling in a table like the above with only the first 3 columns: The source data looks exactly the same as what you see above, the only difference being that in the output we also added a number rows by group. For this one, though, we actually do need to write a little code. And here it is: take the first three columns of the table below, and add a final column with index numbers sorted by and restarting with each group:Īnd of course there is. I've never used the PARTITION function in SQL, so I checked in with him just to confirm what he was after. Actually, that's a paraphrase… what he really asked was:Īny thoughts on how to produce something like a ROW_NUMBER function with PARTITION from T-SQL? Similar to this: In the Advanced category, under Editing options, select or clear the Enable fill handle and cell drag-and-drop check box to display or hide the fill handle.After one of my previous sorting posts, Devin asked if we can number rows by group. In Excel 2007, click the Microsoft Office Button, and then click Excel Options. In Excel 2010 and later, click the File tab, and then click Options. The fill handle displays by default, but you can turn it on or off. For example, to start a numbered list by using 000-001, you enter the formula =TEXT(ROW(A1),"000-000") in the first cell of the range that you want to number, and then drag the fill handle to the end of the range. To enter specific sequential number codes, such as purchase order numbers, you can use the ROW function together with the TEXT function. For more information, see Create or delete an Excel table in a worksheet. All rows that are added at the end of the table are numbered in sequence. If you are using the ROW function, and you want the numbers to be inserted automatically as you add new rows of data, turn that range of data into an Excel table. ![]() You can manually update the numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range. The sequence may be interrupted if you add, move, or delete rows. ![]() These numbers are updated when you sort them with your data. For more information, see Display or hide the fill handle. Tip: If you do not see the fill handle, you may have to display it first.
0 Comments
Leave a Reply. |