Cosmos DB date modeling issue: same column has List and Value

[karthikds]

Hi Team,

I am connecting to Cosmos DB from Power BI. I have issue in fetching data where a column has both list array and a record or list array and value. Please see the screenshot for sample. 

I need to know how to extract data in this scenario. Source data cannot be modified.

Thanks in Advance.

Regards,

Karthik

[Anonymous]

Hi @karthikds ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @karthikds ,

To my knowledge, you could use Table.FromRecords function to convert record to table. 

Hope these help:

https://community.powerbi.com/t5/Desktop/Expand-column-containing-list-of-records/td-p/425888

https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

https://guyinacube.com/2016/06/07/expand-lists-in-power-bi-also-a-look-at-m-syntax/

https://stackoverflow.com/questions/36506559/how-to-transform-list-type-in-power-query-m

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi @karthikds ,

I would probably convert both to a table and then expand. Please check this article for more details: 
How to expand a column that cannot be expanded in Power BI and Power Query in Excel – The BIccountan...

To transfer a list to a record you can use: Table.FromColumns( { YourList } )
And a record to a table: Table.FromRecords( { YourRexord } )
!! You need the additional curly brackets because these functions need a list/array of these input values.

[amitchandak]

@karthikds , Maybe separate these into two-column and then take action.

@ImkeF , any advice on this

[StefanoGrimaldi]

try creating 2 tables one with the records datas, and another with the list data, if need do some basic tranformation to each to match column name etc, and them append them in a new query, disablaling the first 2 after the new one its creating with the appended data from both.