In a comment on my post on Creating Sequences of Integers And Characters In Power BI/Power Query Lists a reader, Paul G, asked me the following question:
can you reverse this? e.g i have a list (1,2,3,5,7,8,9,12,13,14,15) can i convert this to (1-3, 5 ,7-9,12-15)
This got me thinking… I was sure it could be done in M, but would it be possible using just the UI? As far as I can see, it isn’t – there’s one crucial thing I can’t do – but I would be interested to see if anyone else can come up with a no-code solution.
The Almost UI-Only Solution
Here’s the source data, an Excel table called SourceTable:
Here’s what I did to create the ranges in a new Power Query query:
- Load the table into Power Query and set the Numbers column to have a data type of Whole Number
- Sort the Numbers column in ascending order (even though in this case it already is)
- Add an Index Column. It doesn’t matter what number it starts at (though I started it at one) but it does matter that it has a consecutive list of whole numbers in it:
- Select both columns in the table, go to the Add Column tab in the query editor and under the Standard button select Subtract to add a new column containing the value of [Numbers]-[Index]:
Each distinct value in this column equates to a range of numbers in our final output – for example the rows that have the value 0 in this table will become the 1-3 range in the output. - Do a Group By on this table, grouping by the Inserted Subtraction column and finding the minimum and maximum values in each range:
- Remove the Inserted Subtraction column because we don’t need it any more:
- Select the Min and Max columns and then do a Merge Columns to combine these two sets of values using a dash:
- You now have your range names, but in the case of 5-5 you need to just return 5, so add a Conditional Column to handle this like so:
- Set the resulting column to have a data type of text:
- Remove all other columns apart from Custom:
- And finally, combine all the values together into a single comma-delimited list. This is where the UI lets you down, at the last step! Gil Raviv shows one way to solve this problem in a recent post, and then in a follow-up posts a much more elegant solution using M from Imke Feldmann which is my preferred approach too. I won’t repost all the steps (which are very well described here anyway) but basically you do another Group By, then alter the code of the resulting step to use Text.Combine() to aggregate the text.
Job done! If anyone from the Power Query team is reading this blog, here’s a message for you: please give us a new option in the Group By dialog to concatenate all the text values in a column, rather like the DAX ConcatenateX() function. Thanks!
The Pure M Solution
Of course you would like to see a pure M solution too, wouldn’t you? Here you go:
[sourcecode language=”text” padlinenumbers=”true”]
let
//The list to find ranges in
Source = {1,2,3,5,7,8,9,12,13,14,15},
//Sort in ascending order
SortedSource = List.Sort(Source, Order.Ascending),
//Get a list of all the positions in the list
//rather like adding an index column
Positions = List.Positions(SortedSource),
//Create a list of all the groups by subtracting
//each number from its position in the list
Groups = List.Transform(Positions, each SortedSource{_}-_),
//The function to use with List.Accumulate
RangeAccFunction = (state, current) =>
let
//Get the current number from the iteration
CurrentNumber = Text.From(SortedSource{current}),
//Get the current group number
CurrentGroup = Groups{current},
//Get the previous group number
LastGroupIndex = if current=0 then 0
else current-1,
LastGroup = Groups{LastGroupIndex},
//Get the next group number
NextGroupIndex = if current=List.Max(Positions)
then 0
else current+1,
NextGroup = Groups{NextGroupIndex},
//Generate the text for this iteration
TextToAdd =
if current=0 then CurrentNumber
else
if CurrentGroup<>LastGroup
then ", " & CurrentNumber
else
if CurrentGroup<>NextGroup
then "-" & CurrentNumber
else ""
in
//Return the previous text plus
//the newly generated text
state & TextToAdd,
//Use List.Accumulate to iterate over the list of positions
//And generate the text for the ranges
Output = List.Accumulate(Positions, "", RangeAccFunction)
in
Output
[/sourcecode]
The output in this case is just the text we’re looking for:
This turned out to be a great opportunity to use the List.Accumulate() function; once again, Gil Raviv has a great post describing this function and how it can be used. In this case I’m using a very similar approach to the one above to group the numbers, then declaring a function called RangeAccFunction that can be passed to List.Accumulate() to generate the output text. Again, I would be curious to see other solutions!
You can download the sample workbook for this post here.
Hi Chris,
very nice solutions!
I prefer the 1st one & would modify it a bit:
In step 5 return _[Numbers] instead of Min and Max, which would then be a list of numbers within each group.
With this you can replace steps 6-10 with this single step:
= Table.AddColumn(#”Grouped Rows”, “Custom”, each if List.Count([Partition])=1 then Text.From([Partition]{0}) else Text.From(List.Min([Partition]))&”-“&Text.From(List.Max([Partition])))
So with the list in place, you just pick whatever you need and how you need it for your text-field 😉
Yes, but the first solution was supposed to be no-code!
Oh, I see – sorry, was a bit too quick on that.
Chris,
After Step 10 of the no-code solution, can you:
11. Transpose the (single column) table to create one row of the ranges
12. Merge all the columns back into one with a , separator?
Yes, I thought of that but then you have to select all the columns to do the merge, which means the column names are hard-coded in the merge step, which means that it won’t work if you add more rows to the table 🙁
Chris, the UI will never let you down 🙂
Maybe A.C. Milan can let me down sometimes, but not Power Query’s UI.
The last 2 steps should be to transpose your table and then do the merge of all the columns
Then you’ll have to use the formula bar and delete the hardcoded part and use Table.ColumnNames 😛 – you’ll still be in the UI tho!
Miguel, read my reply to Steve (who said the same thing) above – this will break if the number of ranges changes
After step 10:
…
11. #”Transponse Table” = Table.Transpose(#”Remove Columns”),
12. Merged = Table.CombineColumns(#”Transponse Table”, Table.ColumnNames(#”Transponse Table”), Combiner.CombineTextByDelimiter(“, “), “Merged”)
Regards Frank
Yes, but that involves writing code and the initial goal was for this to be a no-code solution!
Exactly what I had in mind!
The UI is the way to go and even if it sometimes doesn’t completely make it, you can always just tweak a part of the code that was generated by the UI to your advantage. I’ve been loving that Table.ColumnNames function for quite a while now
Hi Chris,
I think, there is no way to do this directly from UI but with a little trick we don’t need to write anything…. we can use CTRL+C, CTRL+V .
let
…
…
#”Removed Other Columns” = Table.SelectColumns(#”Changed Type2″,{“Custom”}),
#”Added Index1″ = Table.AddIndexColumn(#”Removed Other Columns”, “Index”, 1, 1),
#”Changed Type1″ = Table.TransformColumnTypes(#”Added Index1″,{{“Index”, type text}}),
#”Pivoted Column” = Table.Pivot(#”Changed Type1″, List.Distinct(#”Changed Type1″[Index]), “Index”, “Custom”),
#”Merged Columns” = Table.CombineColumns(#”Pivoted Column”,{“1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Ranges”)
in
#”Merged Columns”
where #”Removed Other Columns” is a last step which i took from your original query
The other steps are directly from UI…. so, we can get the second argument of Table.Pivot function from #”Pivoted Column” step (CTRL+C) and put it to our last step (#”Merged Columns”) as a second argument of Table.CombineColumns instead of hard-coded column names (CTRL+V). So we have (in some sense) “no-code” solution 😉
After these changes our last step looks like this below
#”Merged Columns” = Table.CombineColumns(#”Pivoted Column”, List.Distinct(#”Changed Type1″[Index]), Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Ranges”)
Of course this solution is for fun only (but works) 🙂
Regards
“please give us a new option in the Group By dialog to concatenate all the text values in a column, rather like the DAX ConcatenateX() function”
I can make a similar argument for a dozen or so functions that are not in the Group By list. My approach is to group by something in the UI and then change the grouping expression in the formula bar. In this specific case, even if you had a Concatenate type function, you would have to create a grouping column to concatenate the text in all of the rows. This means creating a column with the same value in all of the rows, and then grouping by that column.
For example, in the following expression, I’m grouping by a custom column called “Concatenated Text Grouping,” which has the arbitrary value 1 in each row. Initially, I grouped by “Count,” and then changed the entire expression after “each.”
Table.Group(
AddedGroupingColumn2,
{“Concatenated Text Grouping”},
{{“Concatenated Text”, each Text.TrimEnd(List.Accumulate([Groups], “”, (accum, curr)=>
accum&curr&”,”),”,”)}}
)
By the way, in the List.Accumulate function, [Groups] is the column containing text groups (1-3, 5, 7-9, 12-15). I like to think of SumX and ConcatenateX as syntax sugar for List.Accumulate. 🙂
Colin, I’m a bit confused – why you do say you need to create a column with the same value in to group by? You don’t need to have any columns to group by in the UI if you don’t want to. I do this all the time and do it here.
Thank you for this – I needed to do this and I had no idea Power Query was even a thing! Unfortunately, I got completely lost on step 11. Instead, I selected Close & Load, added a column with commas (except 1 empty cell at the bottom), and used the CONCAT() function on the entire range. That gave me my comma-separated string.
Thanks for the solution!!
I noticed a small bug in the M-script version though. If the input is a contiguous list e.g. {1,2,3,4,5} then the output is “1” instead of “1-5”.
I solved it in the following way by adding an additional clause to the “TextToAdd” ifs:
TextToAdd =
if current=0 then CurrentNumber
else
if CurrentGroupLastGroup
then “,” & CurrentNumber
else
if CurrentGroupNextGroup
then “-” & CurrentNumber
else
if current=List.Max(Positions) and CurrentGroup=List.First(Groups)
then “-” & CurrentNumber
else “”
Thanks for the code though!!!