Forum Discussion
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 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!
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 TransformBackToTextAndCombinePaste this code into the advanced editor and follow the steps.
3 Replies
ImkeF , Can you help
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"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 TransformBackToTextAndCombinePaste this code into the advanced editor and follow the steps.
