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
Solution Sagemiguel 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
cpwebbMicrosoft Employee
This blog post has a lot of examples on how you can use Json.FromValue to do this: https://blog.crossjoin.co.uk/2016/10/07/generating-json-in-power-bi-and-power-query/