Forum Discussion
Solved
Unpivot Columns using the list
Dear all, here my question. I have this sample table: I need to create a custom function to split the columns by the /, and then unpivot them to get this: As the original table is made...
Hi Mic1979
let
Source = Your_Source,
Text_Split = Table.TransformColumns(Source,
{{"Stuffing_Box_Material", each Text.Split(_, " / "), type text},
{"Body_Material", each Text.Split(_, " / "), type text}
}),
Expand1 = Table.ExpandListColumn(Text_Split, "Body_Material"),
Expand2 = Table.ExpandListColumn(Expand1, "Stuffing_Box_Material")
in
Expand2Stéphane
Just split each of the columns into Rows, using one of the Advanced Options in the Split Column by Delimiter dialog
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4JLlEwNjTzUdBXQLC1FUKKUhNLclPzSoBs55zUxLzMvHQlHXTlITDlcCWxOtFKTkX5eVWpQNVORYnFxWAhuD5kM1BlFLQJ2olsTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Body Material" = _t, #"Stuffing Box Material" = _t]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {
{"Body Material", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Body Material"),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {
{"Stuffing Box Material", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Stuffing Box Material"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Stuffing Box Material", type text}})
in
#"Changed Type"