Parse "List" Datatype on PowerBI - connecting with CosmosDB v2

[TramLe99]

I am connecting PowerBi to Cosmos DB via Connector v2. Some fields type "List" on Cosmos DB are recognized as "Table" on PowerBI, and 1 field are broken into 2 column, which can't be parse further. Please support me to parse these "List" field on PowerBI

Example: List data field "bot_fyi" are broken into 2 column below

[TramLe99]

Hi @lbendlin @ahadkarimi , while clicking on the Table Link, it always show error while there are actual data for List field in the Cosmos DB. 

I also can't use expand icon in Power Query either. After clicking this, no column is found. The actual data type of this is List. I don't unserstand why PowerBI recognize it as Table

[lbendlin]

Please click next to the Table link, don't click on the link itself.

[ahadkarimi]

@TramLe99,
Verify Cosmos DB Structure ➡️ Ensure list data is correctly formatted.
Use Advanced Editor ➡️ Manually extract lists using M code.
Update Connector ➡️ Ensure you have the latest Cosmos DB connector.
Check Logs ➡️ Look for specific errors in Power BI logs.
If unresolved, consider flattening the data directly in Cosmos DB.

If you encounter any issues, let me know.

[TramLe99]

Hi @ahadkarimi,

- Sorry may I ask how to know whether the data in cosmosDB is correctly format. We have sample data like this 

{"id": "1",
"table_name": "case",
"case_id": "1",
"dupe": [
"case_2",
"case_3"
],
"_rid": "I5ogAJU1xREDAAAAAAAAAA==",
"_self": "dbs/I5ogAA==/colls/I5ogAJU1xRE=/docs/I5ogAJU1xREDAAAAAAAAAA==/",
"_etag": "\"7c008860-0000-0200-0000-66c810de0000\"",
"_attachments": "attachments/",
"_ts": 1724387550
}

and the issue I got is for field "dupe" with list type.

- I tried to change the type from Table to List but after extract it returns back to Table (original issue)

[ahadkarimi]

Hi @TramLe99, check below if you encounter any issues, let me know.
https://community.fabric.microsoft.com/t5/Power-Query/Expression-Error-We-cannot-convert-the-value-t...

Did I answer your question?  If so, please mark my post as the solution!
Your Kudos are much appreciated!  Proud to be a Responsive Resident !

[ahadkarimi]

Hi @TramLe99,

Expand Table Column ➡️ Click expand icon in Power Query.
Repeat for Nested Lists ➡️ Expand until fully flattened.
Combine Columns ➡️ Use Custom Column if split.
Set Data Types ➡️ Ensure correct types.
Clean & Apply ➡️ Filter, clean, then Close & Apply.

Did I answer your question?  If so, please mark my post as the solution!
Your Kudos are much appreciated!  Proud to be a Resolver IV !

[lbendlin]

what do you see when you click next to one of the Table links?