Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
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 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.

 

Need Help
  • slorin's avatar
    slorin
    1 year ago

    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 

8 Replies

  • slorin's avatar
    slorin
    Super User
    1 year ago

    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's avatar
      Mic1979
      Post Partisan
      1 year ago

      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

  • Mic1979's avatar
    Mic1979
    Post Partisan
    1 year ago

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

    Thanks again, really appreciated.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    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"
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    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's avatar
      Mic1979
      Post Partisan
      1 year ago

      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's avatar
        v-nmadadi-msft
        Community Support
        1 year ago

        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