Power Query - Convert the data from XML column into the same column with line break

[Anonymous]

Hi,

I have a file mixed with normal data and with a column of data in XML format.

How can I convert the column from XML format to normal text format with line break?

Here is an example.

Original XML format in 1 column mixed with normal data:

                <CustomField>
                  <Key>Artwork</Key>
                  <Value>Adhesive Label - Paper MD PACKAGING</Value>
                </CustomField>
                <CustomField>
                  <Key>Comments</Key>
                  <Value>PACKAGED COMPLETE FOR FINISHED GOODS</Value>
                </CustomField>
                <CustomField>
                  <Key>Material</Key>
                  <Value>Lid, Top, body; ABS Weight; Steel</Value>
                </CustomField>

Desired result that I want to display in PowerBI:

Best regards,

Emily

[AlB]

Hi @Anonymous 

Is what you show in one cell or spread across one column? If the latter, is each line of what you show in a row in the table? CAn you show a screen pic and copy the table here in text?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

Here is how it looks like in Power Query in XML format for the CustomData column.

I want to split the same row of XML format data into line break.

Any idea how to make this magic?

Thanks.

Text code for XML:

<root><Artwork>Adhesive Label - Paper MD PACKAGING</Artwork><Comments>PACKAGED COMPLETE FOR FINISHED GOODS</Comments><Material>Lid, Top, body; ABS Weight; Steel</Material><Finish>Lid, Top, body; Cool Gray 11C; IDI-OPQ-028 Weight; Chrome; IDI-MPL-001</Finish><PackSpecs>4 PCS PER BROWN CARTON W/LABEL</PackSpecs><DrawingNumber>10750-SFT-SQCAN-ASSY</DrawingNumber><SecondaryLinePrice>1750.7</SecondaryLinePrice><SecondaryUnitPrice>1.7507</SecondaryUnitPrice></root>

Best regards,

Emily

[AlB · Accepted solution]

@Anonymous 

What is it that you are trying to do ultimately? Have you tried the XML parser functionality Power Query has? Perhaps it would be handy in this case? Try the following. Probably not the most elegant solution but i think it will do. You might want to make it into a function if you have multiple rows with XML content. The last step shows the content in the same row separated by a line break. The step before the last one shows each item from the XML in a different row of the table. It wasn't clear to me which one of the two options you meant

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xY9LC4JAFIX/ysW1odDSlflKfJJSC3Mx5iWHxkZ0LPz3RUoEFYGb7vLjnMP9skza96q6PBh9J3htU2TlAyC83Rj0cBgDeiuuvD2NVHnib70tYT1OzbLCjl4QfFIggwXEpMEWAhNi3fB0xw2dafWl9HlV+fn3XD2D1zWeRTfDb7SwTDCiIPat1AI72oDthm6yvlMniszk/4IBEdhSwmYI+rSUIeWNDAUvBw30VQI7pMdKaJAIRDbHTsrzGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.SplitAny([Column1],"<>")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> "" and [Custom] <> "                " and [Custom] <> "                  ")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.Contains([Column1],"<" & [Custom] & ">") then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Column1"}, {{"Custom", each Text.Combine([Custom], Character.FromNumber(10
)), type text}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB Thank you very much for your suggested solution.

I tried it in my file and modifed a bit on the M query and it's working well.

Have a great day!

Best regards,

Emily