Forum Discussion
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...
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.
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
TransformBackToTextAndCombine
Paste this code into the advanced editor and follow the steps.