Forum Discussion
Kudo CollectorHow to save a "List" column for later use?
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: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
So this a limitation on the Lakehouse/Warehouse end.
I've opened an idea so they increase the Text field size.
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=fd452033-4bb2-ee11-92bd-000d3a0d7518
Please vote
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:
- 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. - 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.
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 .
iBusinessBII 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?
cpwebbMicrosoft Employee
If you're hitting a limit on the maximum length of a text value somewhere, then I guess the only alternative is to split the text across multiple rows (something like this https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/). This will cause complications downstream though. Why do you need to retain the nested structures? I think at this point it would be easier to expand them in the dataflow.
- iBusinessBI
I am working with API source. I want to run once a day and save the results in a Datawarehouse. The day after, I will run API on a different day....
Dataflow itself doesn't allow me to work incrementally with API source, so I need to save the results in Warehouse / Lakehouse. How can I store the whole text in Warehouse / Lakehouse?