Range and Values in Cell, How to Expand?

[kcummins]

Hello,
I have a table and one column contains cells that each have a list of text within them. These lists contain both values and value ranges, together as a single text string (so not an actual PQM "List"). I want to turn this into a column of all the contained values, also as a text string. For example:

Cell in Column                                             Cell in NewColumn

124, 204-206, 237, 254-258, 373     --->     124, 204, 205, 206, 237, 254, 255, 256, 257, 258, 373

I tried several variations of NewColumn = { [Column] } but haven't been able to make anything work. It's easy enough to replace the "-" to "..", but I can't seem to get the list brackets to read the Column reference as an actual input.

Thank you so much for the help, I feel like I've been dancing around the solution for hours!

[v-frfei-msft]

Hi @kcummins ,

Please refer to the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQy0VEwMjDRNTIwAzKMzYGEKZBnaqGjYGxurBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", type text}, {"Column1.3", Int64.Type}, {"Column1.4", type text}, {"Column1.5", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}}, "en-US"), "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each if[Column1.2] = null then [Column1.1] else [Column1.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}, {"Column1.1", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "Custom.1", each List.Numbers([Column1.1],[Custom]-[Column1.1]+1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.1",{"Column1.1", "Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom", each 1),
    #"g" = Table.Group(#"Added Custom2", {"Custom"}, {{"Custom.1", each Text.Combine(List.Transform([Custom.1], (x) => Number.ToText(x)), ","), type text}}),
    #"Removed Columns2" = Table.RemoveColumns(g,{"Custom"})
in
    #"Removed Columns2"

[ImkeF · Accepted solution]

Hi @kcummins 

if you want to add a column with the transformed table instead of transforming the existing column, you can use the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQy0VEwMjDRNTIwAzKMzYGEKZBnaqGjYGxurBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    SplitByComma = Table.AddColumn(Source, "Custom", each Text.Split([Column1], ", ")),
    SplitRanges = Table.AddColumn(SplitByComma, "Custom.1", each List.Transform([Custom], (x) => Text.Split(x, "-"))),
    CreateListsFromAllElements = Table.AddColumn(SplitRanges, "Custom.2", each List.Combine( List.Transform([Custom.1], (x) => {Number.From(List.Min(x))..Number.From(List.Max(x))}))),
    TransformBackToTextAndCombine = Table.AddColumn(CreateListsFromAllElements, "Custom.3", each Text.Combine(List.Transform([Custom.2], Text.From), ", "))
in
    TransformBackToTextAndCombine

Paste this code into the advanced editor and follow the steps. 

[amitchandak]

@ImkeF , Can you help