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.
Kudo CollectorHi 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?
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.- iBusinessBI
Sorry, no, it doesn't help.
Look at my original screenshot. My List is not composed with comma-separated text strings, but it is a COMPEX List, which contains RECORDS that contain Lists, which contain more records, etc....
So Text.Combine doesn't work
Hi iBusinessBI ,
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.
Thank you