Converting Lists Of Numbers To Text Ranges In Power Query

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:

image

Here’s what I did to create the ranges in a new Power Query query:

  1. Load the table into Power Query and set the Numbers column to have a data type of Whole Number
    image
  2. Sort the Numbers column in ascending order (even though in this case it already is)
    image
  3. 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:
    image
  4. 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]:
    image
    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.
  5. Do a Group By on this table, grouping by the Inserted Subtraction column and finding the minimum and maximum values in each range:
    imageimage
  6. Remove the Inserted Subtraction column because we don’t need it any more:
    image
  7. Select the Min and Max columns and then do a Merge Columns to combine these two sets of values using a dash:
    image
    image
  8. 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:
    image
    image
  9. Set the resulting column to have a data type of text:
    image
  10. Remove all other columns apart from Custom:
    image
  11. 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.
    image

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:

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

The output in this case is just the text we’re looking for:

image

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.

13 thoughts on “Converting Lists Of Numbers To Text Ranges In Power Query

  1. 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 😉

  2. 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!

  3. 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

  4. 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

  5. “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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s