xml.document

[Anonymous]

Hello,  One of the column I have is an xml type.

It contains information as follow:

<?xml version="1.0" encoding="utf-16"?>
<string><DLTKVisionMessage><ReturnCode>ErrDataVal</ReturnCode><ReturnDesc>Data Validation Error</ReturnDesc><Detail><ValidationResult>
<Message>
<Table>EMAllCompany</Table>
<Column>Org</Column>
<ColValue>1-CO-000-048</ColValue>
<RowNum>1</RowNum>
<PrimaryRecKeyVal>64122</PrimaryRecKeyVal>
<MsgCode>VAL1001</MsgCode>
<MsgDesc>Value not in the master/code table!</MsgDesc>
</Message>
</ValidationResult></Detail></DLTKVisionMessage></string>

I would like to know how to import in 3 new columns, the PrimaryRecKeyVal = 64122, MsgCode=VAL1001 and MsdDesc ="Value not in the master/code table!" using DAX to obtain the following result

ex:

Column1        Column2    Column3

64122             VAL1001     Value not in the master/code table!

[ElenaN · Accepted solution]

Hello,

In Power Query you can add a new column for each case using the following formula:

= Text.BeforeDelimiter(Text.AfterDelimiter([Column1], "<MsgCode>"), "</MsgCode>")

For each column you have to modify the search text accordingly.

Regards,

ElenaN

[Anonymous]

Hello,

Thanks for the solution.  It seems to work well.  Do you have the equivalent function in DAX?

[v-chuncz-msft]

@Anonymous ,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/retrieve-text-string-from-cells-in-excel/m-p/305533#M135131