Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've seen a lot of examples of changing a list to a csv type format, but how do you go the other way?
With this data:
Header
{"A","B","C ee"}
{"A","D","C ee"}
I'm hoping to end up with this:
Header
list
list
...so I can expand the data to new rows. However, the data is of type "text". I can't, for the life of me, figure out how to switch it over a type list. Isn't that the format of a list above?
Solved! Go to Solution.
Found it! A bit more complicated than I hoped for, but it works:
= Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([Header]))
Now if only I could remember how to do it "in column" where it replaces the value in the existing column instead of adding a new one, that'd save another step or two.
That was my first attempt... but this is what it does when you expand one of the list items:
I'm hoping for
List
A
B
C ee
My appologies. Please look into Text.Split([Header], ",")
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RcoxR0olRcgKTzgqpqTFKtUqxOkhSLqhSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","{","",Replacer.ReplaceText,{"Header"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"Header"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","""","",Replacer.ReplaceText,{"Header"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Split([Header], ",")),
Custom = #"Added Custom"{0}[Custom]
in
Custom
Yes - that does function and was what I was hoping was just a workaround :). I was hoping there was a simple way to say "treat the text that's formatted as a list... as a list!". Something like
= Table.AddColumn(#"Promoted Headers", "Custom", each Value.As([Header], List.Type))
But that doesn't work: Expression.Error: We cannot convert the value "{"A", "B", "C ee"}" to type List.
Details:
Value={"A", "B", "C ee"}
Type=[Type]
Found it! A bit more complicated than I hoped for, but it works:
= Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([Header]))
Now if only I could remember how to do it "in column" where it replaces the value in the existing column instead of adding a new one, that'd save another step or two.
Hello, maybe a late reply but to transform your existing column to a list type without adding an additional column, try this method:
= Table.TransformColumns("tableName", {"ColumnName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)})
The "transformOperation" argument in "TransformColumns" can have multiple operations. So if you want to convert multiple columns to a list type:
= Table.TransformColumns("tableName", {
{"column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)},
{"column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}
})
@ianbruckner sorry if I misunderstood, but doesn't Text.ToList() do just that?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RcoxR0olRcgKTzgqpqTFKtUqxOkhSLqhSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.ToList([Header]))
in
#"Added Custom"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.