Forum Discussion

kcummins's avatar
kcummins
Frequent Visitor
6 years ago
Solved

Range and Values in Cell, How to Expand?

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 PQ...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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.