Issue expanding column of records

[toafk]

My data is JSON. After doing af lot of transformation, I end up with an issue. When expanding the then column with records, I loose a lot of data.

This record fails when expanding

This record expands correct.
  

Clearly Power Bi does not undestand the example since it writes "a" instead of "Value".

The data are descriping a resource. The diffence in the underlying XML is, that unsuccesfull records have one CustomProperties  value - and succesfull records have more than one.

Succesfull example

<CustomProperties>

<Property PropertyID="{2EB29505-BF4B-4E87-BCF5-621CDA954407}">

<Value>Silent Zone</Value>

<Value>Telephone</Value>

</Property>

<Property PropertyID="{3A2A0DCD-308C-446A-9E38-352C42A6A053}">

<Value>Workspace</Value>

</Property>

</CustomProperties>

Unsuccesfull record example

<CustomProperties>

<Property PropertyID="{3A2A0DCD-308C-446A-9E38-352C42A6A053}">

<Value>Workspace</Value>

</Property>

</CustomProperties>

How should this be handled in Power Query?

[v-alq-msft]

Hi, @toafk 

I wonder which connector you are using. The problem is related to the structure of the file. Could you show us the transformations applied to the data? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[Vera_33]

Hi @toafk 

I am not sure what value you want to extract, and you provided XML instead of JSON...are you using some sample query generated by PBI? Have you tried to add a custom column to extract XML or JSON? Can you provide your M code?