Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

converting column to XML string

‎04-13-2019 05:30 PM

Hi,

 

I'm new to Power BI. But I'm trying to POST a SOAP request which takes input as xml. I have a query (name Headerids) that has a column name xmltaskids. I would like to convert that Column to the below xml String. Please help me how can I convert  ALL the records to a single String so that it represents XMLbody

Please note:

Each row in the column --> has XML tag and Value. 

e.g. 1st row is <taskId>28161430</taskId> in the column

2nd row is <taskId>27739924</taskId>

 

body = "<Envelope xmlns=""http://schemas.xmlsoap.org/soap/envelope/"">
<Body>
<readTasks xmlns=""http://mercury.com/ppm/pm/service/1.0"">
<taskId>28161430</taskId>
<taskId>27739924</taskId>
<taskId>27739925</taskId>
<taskId>27739926</taskId>
<taskId>27739927</taskId>
<taskId>28161430</taskId>
</readTasks>
</Body>
</Envelope> ",

 

 

Labels:
Message 1 of 2
767 Views
0
1 REPLY 1
d_gosbell
Super User
Super User

‎04-14-2019 07:20 PM

There might be other ways of doing this, but used Table.AddColumns to add a column with a constant value of 1. Then I used Table.Group to group all the rows on that value and used Text.Combine to join that xmltaskids column.

 

eg.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOLkkszvZMAbNTjSwMzQxNjA0gMvrIUkqxOlh1mJsbW1oamZCsw5RkHWYk6zAnQQc+n8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [xmltaskids = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xmltaskids", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Group", each 1),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"TaskIds", each Text.Combine( [xmltaskids]), type text}}),
    #"FirstRow" = #"Grouped Rows"{[Group=1]}[TaskIds],
    body = Text.Combine({"<Envelope xmlns=""http://schemas.xmlsoap.org/soap/envelope/"">
<Body>
<readTasks xmlns=""http://mercury.com/ppm/pm/service/1.0"">", #"FirstRow",
"</readTasks>
</Body>
</Envelope> "})
in
    body
Message 2 of 2
740 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All