Forum Discussion

iBusinessBI's avatar
iBusinessBI
Icon for Kudo Collector rankKudo Collector
2 years ago
Solved

How to save a "List" column for later use?

I am starting to work with Dataflow Gen 2.

I get data from API and I want to save in in a destination for later use.

One of my columns contains "List" type of values and I want to KEEP IT CLOSED AS-IT-IS without expanding for later consumption by another data flow I'll build later.

How can I achive this?

 

Need Help
  • miguel's avatar
    miguel
    2 years ago

    Perhaps what you're looking for is to save the full response from the API as a binary, save it to the lakehouse as a binary and then consume the binaries through a different dataflow.
    If this is in line with your expectation, I'd suggest raising a new idea using the link below:

     https://aka.ms/FabricIdeas

     

    You could also post an idea for the lakehouse team to increase the maximum string size. As mentioned before, this is not a limitation of the Dataflow engine, but rather a limitation of the destination which cannot handle more than X number of characters for a string and how many characters your string has.

    All of the previously shared solutions are the ways that you can handle things in Dataflows Gen2 today:

    • Expand the complex values as those data types are not supported in the destination that you're mentioning
    • Transform the complex values to a text string and save it as a string.
      • Furthermore, you could encode the string so it shortens things and save it that way. It could also make the number of character expand tremendously, so it might not be a one-size-fits-all solution

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi iBusinessBI ,

    Thank you for using Fabric Community

     

    Here are the methods to save a "List" column for later use in MS Fabric Dataflow Gen2:

    1. Serialize to JSON:
      Within the Dataflow:
      Use the toJSON() function to convert the "List" column into a JSON string.
      Create a new column to store the JSON string.
      Destination Sink:
      Choose a sink that accepts JSON strings, such as Data Lake Storage Gen2 or Azure SQL Database.
      Later Use:
      In the subsequent Dataflow, use fromJSON() to deserialize the JSON string back into a list.

    2. Custom Sink (if necessary):
      Development:
      Create a custom sink specifically designed to handle the "List" column in its desired format.
      Integration:
      Integrate this custom sink into your Dataflow pipeline.

     

    Remember: There is no one-size-fits-all solution, and the best approach will depend on your specific data, transformations, and preferences.

    Hope this is helpful. Please let me know incase of any queries.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi iBusinessBI ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond back with the more details and we will try to help .

      • iBusinessBI's avatar
        iBusinessBI
        Icon for Kudo Collector rankKudo Collector
        2 years ago

        I have managed to create a string field in Dataflow, but when I save it to a Lakehouse or a Warehouse it gets truncated (limited ti 7600 charachters). While in Dataflow I see the whole text (19000 characters). How can I save the whole text?

    • iBusinessBI's avatar
      iBusinessBI
      Icon for Kudo Collector rankKudo Collector
      2 years ago

      Hi Anonymous 
      I've tried to use toJSON([My List Field]) but I don't find such a function at all in Power Query / Dataflows.

      What do I miss?
      Did you try this function yourself?
      I just get an error:
      "The import toJSON matches no exports. Did you miss a module reference?

      Details
      Reason = Expression.Error"
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi iBusinessBI ,

        Apologies for the confusion, I rechecked your ask once again. 
        Could you please check whether below steps may help you?

        Scenario -


        You can convert the list of value to comma seperated values in a new column -

        Text.Combine([Column Name], ",")


        After saving you will able to convert list into comma seperated values and can store it in any destination.


        Final Destination -


        Hope this is helpful. Please let me know incase of any further queries.

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    2 years ago

    miguel is right. The best approach is to use Json.FromValue. You can use Text.FromBinary(Json.FromValue([ListColumn])) to store it as a JSON text string. Your consumers can then use Json.Document([JSONasText]) to parse the JSON and get the lists, records, etc. back.

     

    Pat