Unpivot Columns using the list

[Mic1979]

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 up with roughly 2.000 rows, I have the feeling that using the lists is improving the efficiency of the query.

Could you help?

Thanks.

[v-nmadadi-msft]

Hi @Mic1979  ,
Thanks for reaching out to the Microsoft fabric community forum.

To identify the most efficient method, I replicated your scenario and tested both splitting by delimiter and by using Lists. I found that both methods loaded in a similar timeframe; however, it's important to note that my dataset contained approximately 1,000 rows. I wouldn’t say there is a significant advantage to using lists in this case. The advantage of using "Split by Delimiter" is that it is much simpler unless additional complex transformations are required after splitting the rows. In such scenarios, using lists may be the better option.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards

[Mic1979]

Hello 

thanks for your feedback. Would it be possible for you to share a code with the lists I can use?

Thanks.

[v-nmadadi-msft]

Hi @Mic1979 ,
I have used the code provided by @slorin and modfied it slightly so that it would fit my 1000 row data model correctly.
Thanks and Regards

[Mic1979]

I thought you made a code with the list.

My bad.

Thanks.

[ronrsnfld]

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"

[Mic1979]

Thanks. Is it not quicker in terms of query efficiency to use the lists? I have big data to manage..

Thanks again, really appreciated.

[slorin · Accepted solution]

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
Expand2

Stéphane 

[Mic1979]

Hello slorin,

thanks for your code, I will accept it as a solution. If I wanted to make it more general, just to check to split those columns where the " / " is present, how to do this?

Thanks again