Forum Discussion
xml.document
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!
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
3 Replies
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 ,
You may refer to the post below.
https://community.powerbi.com/t5/Desktop/retrieve-text-string-from-cells-in-excel/m-p/305533#M135131
