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
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- miguel
Community Admin
Hey! Please try the function below:
https://learn.microsoft.com/en-us/powerquery-m/json-fromvalue
the concept is as the one mentioned before. Transform a complex value into something that can be stored in a destination such as a text string.
Below is a sample code to demonstrate how this might work:
let SampleList = {1..20,{1..2}}, Transformation = Lines.FromBinary(Json.FromValue(Source)) in TransformationNote: The only situation that you might encounter is when you might have text strings that perhaps are too large for the fields of your destination, so if perhaps the field in a table of your data destination can only hold a maximum of X characters, if your string has more than X amount then that could cause issues. It might also be quite resource intensive to first evaluate a complex value and transform it into a text string. This is because things in Power Query are lazily evaluated, so are only evaluated when needed.