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...
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 

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