This is not first post about usage of Power Query Excel addin (“Get & Transform” functionality in Excel 2016) in scope of its integration with SAP Business ByDesign.
First was about Date format in JSON response
http://scn.sap.com/community/business-bydesign/blog/2015/10/07/odata-queries-json-and-date-format
Second – about general usage of Power Query to get reports though OData
http://scn.sap.com/community/business-bydesign/blog/2015/10/07/using-sap-bydesign-odata-in-microsoft-power-query

In my post I would like to share another experience – reading web services using Power Query.
Unfortunately, some very important information is not available in data sources, e.g. Accounts Relationships and Pricing Conditions (price lists, customer specific discount lists etc.). Hope someday we will get this in reports. However, currently, we can get this info only from

  • UI manually, almost impossible to do if you have X tenants and XXX customers with specific price lists
  • Web services – Jedi path, require knowledge and skills, or trustable tools, that can be checked

To our luck, with every new release we get more and more web services.

Basic idea of web service usage

  1. Make POST http request with XML
  2. Parse XML response

Firstly, we can make queries and test everything in SOAP UI. Just because it is easier to understand how web services work in SOAP UI examples. Following two posts help to get basic knowledge

Assume that you know how to create XML query in SOAP UI.
Now, step by step about PQ part.
I’ve started my journey from nice post of Chris Webb http://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/
Got basics of http POST request in M code (Power Query programming language)

WS_query = Web.Contents(

URL_ADDRESS,

[Content=Text.ToBinary(PostContents)]),

In our case with ByD, PostContents is equal to XML request.
Another good example from Curt Hagenlocher http://stackoverflow.com/questions/28361628/how-to-post-a-multipart-form-data-using-power-querys-web-contents

Let

actualUrl = “http://some.url“,

record = [__rdxml=”some data”],

body = Text.ToBinary(Uri.BuildQueryString(record)),

options = [Headers =[#”Content-type”=”application/x-www-form-urlencoded”], Content=body],

result = Web.Contents(actualUrl, options)

in

result

This code sample shows, that second parameter of Web.Contents can be complex enough. In official documentation on MSDN you may full list of options for second parameter

For our web service request to ByD we need Headers, Content, and Timeout (in some cases, but be careful). Consider web service Query Accounts
After we make basic query in SOAP UI we have

  • Headers

  • XML query


I’m not a specialist in web technologies, so method of trial and errors revealed that important for request only following headers

  • Authorization
  • #”Content-Type” = “text/xml;charset=UTF-8”,

Rest

  • SOAPAction – query works without it, but I’m not sure that it will work for other queries. So probably we will need it.
  • #”Connection” = “Keep-Alive” – suppose in some cases it is useful. Maybe someone can advise when use it and when omit?

Others – question for me. I decided exclude them from my queries.
Authorization looks not convenient for reading by humans. After playing around I found that it is in Base64 encoding and can be received in PQ by following code
Authorization = “Basic ” & Binary.ToText(Text.ToBinary(Login & “:” & Password))
Typical content of query

“<?xml version=””1.0″” encoding=””UTF-8″”?>

<soapenv:Envelope xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/“” xmlns:glob=””http://sap.com/xi/SAPGlobal20/Global“”>

<soapenv:Header/>

<soapenv:Body>

<glob:CustomerByIdentificationQuery_sync>

<CustomerSelectionByIdentification>

<SelectionByInternalID>

<InclusionExclusionCode>I</InclusionExclusionCode>

<IntervalBoundaryTypeCode>1</IntervalBoundaryTypeCode>

<LowerBoundaryInternalID>1111</LowerBoundaryInternalID>

</SelectionByInternalID>

</CustomerSelectionByIdentification>

<ProcessingConditions>

<QueryHitsUnlimitedIndicator>true</QueryHitsUnlimitedIndicator>

</ProcessingConditions>

</glob:CustomerByIdentificationQuery_sync>

</soapenv:Body>

</soapenv:Envelope>”

Looks pretty simple for one account. To get several accounts we have to repeat highlighted block with corresponding Account ID. So we need a list of

“<SelectionByInternalID><InclusionExclusionCode>I</InclusionExclusionCode><IntervalBoundaryTypeCode>1</IntervalBoundaryTypeCode><LowerBoundaryInternalID>” & AccountID & “</LowerBoundaryInternalID></SelectionByInternalID>”)

Not a problem for PQ. Start from table ACCOUNTS, that contains list of Account IDs which data we want to get from web service.

Couple lines of code

Accounts = Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content]),

Query_List = List.Transform(Accounts, each “<SelectionByInternalID><InclusionExclusionCode>I</InclusionExclusionCode><IntervalBoundaryTypeCode>1</IntervalBoundaryTypeCode><LowerBoundaryInternalID>” & _ & “</LowerBoundaryInternalID></SelectionByInternalID>”)

Give us

Our resulting XML request is a string, long, long string. So list should be converted to text, to be able to join it to header and footer of XML.

Query_Text = Text.Combine(Query_List)

gives Text without delimiters. For XML it is doesn’t matter. Less characters – less size. OK, we are ready to make full text of request in PQ:

Request_body = “<?xml version=””1.0″” encoding=””UTF-8″”?>

<soapenv:Envelope xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/“” xmlns:glob=””http://sap.com/xi/SAPGlobal20/Global“”>

<soapenv:Header/>

<soapenv:Body>

<glob:CustomerByIdentificationQuery_sync>

<CustomerSelectionByIdentification>”

&

Query_Text

&

“</CustomerSelectionByIdentification>

<ProcessingConditions>

<QueryHitsUnlimitedIndicator>true</QueryHitsUnlimitedIndicator>

</ProcessingConditions>

</glob:CustomerByIdentificationQuery_sync>

</soapenv:Body>

</soapenv:Envelope>”

And finally, we can make a web request

WebService_query = Binary.Buffer(Web.Contents(“https://my” & TenantId & “.sapbydesign.com/sap/bc/srt/scs/sap/querycustomerin1?sap-vhost=my” & TenantId & “.sapbydesign.com HTTP/1.1”,

[Headers=[#”Content-Type” = “text/xml;charset=UTF-8”,

#”Authorization” = Authorization ],

Content=Text.ToBinary( request_body )])),

URL we copy from SOAP UI, it is provided by WSDL file. I use Binary.Buffer to load result in memory to

  • avoid occasional additional queries of web service. Don’t know if it is possible, but just in case
  • Make easier further work with data

We know that response of web service is XML. We are lucky, because in PQ it is very easy to parse XML, simply expanding nodes. Each XML subtree is a table object in PQ response

Open as XML

We got simple table

We need Body of response, so click on Table in Body row

No choice – click on Table

Again no choice – click on Table

Where is my data? Click on Table

Good, I bet, that data in Customer’s row. Before drilldown to table we can select cell with Table and check data in preview area

We have two options here

  1. Table3{[Name=”Customer”]}[Table]

  2. Filter + Remove column

    #”Filtered Rows2″ = Table.SelectRows(Table3, each ([Name] = “Customer”)), #”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows2″,{“Name”})

In 1st option Table auto-expands with full list of fields in it, that most probably not what we want. I went with 2nd option – left necessary table and then expand it manually, choosing fields that I need. Last steps in code view

#”Imported XML” = Xml.Tables(WebService_query),

Table = #”Imported XML”{1}[Table],

Table1 = Table{0}[Table],

Table2 = Table1{0}[Table],

Table3 = Table2{0}[Table],

#”Filtered Rows2″ = Table.SelectRows(Table3, each ([Name] = “Customer”)),

#”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows2″,{“Name”})

What I like in Power Query, is that I don’t have to program all of this transformations. Development is much faster than with VBA or other programming language. PQ is desinged for work with data.
OK, Next step is to expand table, we can choose fields from menu easily.

Relationship is a sub-table itself

One more expand

Where CRMH02-2 – Is Ship-To Party Of BUR001-1 – Has Contact Person Great, we got relationships, that are not available in data sources for reports! Final code

let

GetListOfAccountsGeneralData = (Accounts as list) =>

let

TenantId = Number.ToText(Excel.CurrentWorkbook(){[Name=”TENANT_ID”]}[Content]{0}[Column1]),

Login = Excel.CurrentWorkbook(){[Name=”USERNAME”]}[Content]{0}[Column1],

Password = Excel.CurrentWorkbook(){[Name=”PASSWORD”]}[Content]{0}[Column1],

Query_List = List.Transform(Accounts, each “<SelectionByInternalID><InclusionExclusionCode>I</InclusionExclusionCode><IntervalBoundaryTypeCode>1</IntervalBoundaryTypeCode><LowerBoundaryInternalID>” & _ & “</LowerBoundaryInternalID></SelectionByInternalID>”),

Query_Text = Text.Combine(Query_List),

request_body = “<?xml version=””1.0″” encoding=””UTF-8″”?>

<soapenv:Envelope xmlns:soapenv=””http://schemas.xmlsoap.org/soap/envelope/“” xmlns:glob=””http://sap.com/xi/SAPGlobal20/Global“”>

<soapenv:Header/>

<soapenv:Body>

<glob:CustomerByIdentificationQuery_sync>

<CustomerSelectionByIdentification>”

&

Query_Text

&

“</CustomerSelectionByIdentification>                                               

<ProcessingConditions>

<QueryHitsUnlimitedIndicator>true</QueryHitsUnlimitedIndicator>

</ProcessingConditions>

</glob:CustomerByIdentificationQuery_sync>

</soapenv:Body>

</soapenv:Envelope>”,

Authorization = “Basic ” & Binary.ToText(Text.ToBinary(Login & “:” & Password)),

WebService_query = Binary.Buffer(Web.Contents(“https://my” & TenantId & “.sapbydesign.com/sap/bc/srt/scs/sap/querycustomerin1?sap-vhost=my” & TenantId & “.sapbydesign.com HTTP/1.1”,

[Headers=[#”Content-Type” = “text/xml;charset=UTF-8”,

//                        #”SOAPAction” = “http://sap.com/xi/A1S/Global/QueryCustomerIn/FindByIdentificationRequest“,

//                        #”Connection” = “Keep-Alive”,

#”Authorization” = Authorization ],

Content=Text.ToBinary( request_body )])),

#”Imported XML” = Xml.Tables(WebService_query),

Table = #”Imported XML”{1}[Table],

Table1 = Table{0}[Table],

Table2 = Table1{0}[Table],

Table3 = Table2{0}[Table],

#”Filtered Rows2″ = Table.SelectRows(Table3, each ([Name] = “Customer”)),

#”Removed Columns1″ = Table.RemoveColumns(#”Filtered Rows2″,{“Name”})

in

#”Removed Columns1″,

result = GetListOfAccountsGeneralData(Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content])),

#”Expand Table” = Table.ExpandTableColumn(result, “Table”, {“InternalID”, “Relationship”}, {“InternalID”, “Relationship”}),

#”Expanded Relationship” = Table.ExpandTableColumn(#”Expand Table”, “Relationship”, {“RelationshipBusinessPartnerInternalID”, “RoleCode”}, {“RelationshipBusinessPartnerInternalID”, “RoleCode”})

in

#”Expanded Relationship”

Reasonable question, what happen when we input 20k accounts? Of course, web services have limitation. They should not be used for reporting purpose. However, when data not available from reports – we have no choice. For large list of account we must use paging. There are two options

  1. Paging engine that is provided by web services
  2. “Manual Paging”, or paging on client side

First option is not easy to implement. Therefore, I skip it. In my sample workbook you will find 2nd option. I called it “paging on client side”. General idea is to divide initial requested list of objects on list of small lists, that contain default quantity of objects. Quantity, that be surely digested by web service, e.g. 500 accounts. When I came to this task Chris Webb released small but great post, which helped me to understand magic of List.Generate. Comments to this post are very useful as well. I tried to use Table.AddColumn before, passing parameters of query in tables rows, but Power Query makes request for all rows in one moment, therefore web service going crazy and gives same result for different rows. I couldn’t find a way to make delay between rows when use Table.AddColumn. So, I came to requirement to make loop. In PQ this job is done by List.Generate Code, that will help you to make query for 20k accounts

let

Accounts = Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content]),

Count = List.Count(Accounts),

Step = 500,

Steps = Number.RoundUp(Count / Step),

Delay = 1, // seconds

r = List.Buffer(

List.Skip(List.Generate(

() => [

i = 0,

Page = null

],

each [i] <= Steps,

each let

Accounts_List_To_Query = List.Range(Accounts, Step * [i], Step),

GetPage = (Accs as list) => GetListOfAccountsGeneralData(Accs)

in [

i = [i] + 1,

Page = Function.InvokeAfter(()=>GetPage(Accounts_List_To_Query), #duration(0,0,0, Delay))

],

each [Page]

)

)),

#”Table from List” = Table.FromList(r, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expand Column1″ = Table.ExpandTableColumn(#”Table from List”, “Column1”, {“Table”}, {“Table”}),

#”Expand Table” = Table.ExpandTableColumn(#”Expand Column1″, “Table”, {“InternalID”, “Relationship”}, {“InternalID”, “Relationship”}),

#”Expanded Relationship” = Table.ExpandTableColumn(#”Expand Table”, “Relationship”, {“RelationshipBusinessPartnerInternalID”, “RoleCode”}, {“RelationshipBusinessPartnerInternalID”, “RoleCode”})

in

#”Expanded Relationship”

Conclusion
We can make a simple workbook that pulls data from web service for provided list of accounts. Three parts of such workbook
I.

II.

III. Result

I’m sure, mentioned queries can be optimized, universalized for different web services (up to Expand part).

Downloads

You may download sample workbook from this link.

If interestred, more examples of Power Query usage you may find in my blog.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Manoj Kannaujiya

    Hi Ivan,

    Nice blog ….

    I am looking for ByDesign Migration functionally wherein i can export master data and transactional data with all fields from one ByD system and can upload that data in another ByD System.

    For example.

    I want to export all sales order of old ByD system with all fields, attachment and notes and can upload in new ByD system.

    Same for Master Data also.

    I Checked in the system but i didn’t find any place wherein i can export data with all fields including Attachment and Notes.

    Can you please suggest me the right way to achieve this requirement.

    Thanks,

    Manoj Kannaujiya.

    (0) 
    1. Ivan Bondarenko Post author

      Hi Manoj,

      I’m afraid, I cannot help with this. I work only with reporting objects, with fields of simple type – text, date number etc.

      We also did two migrations from SAP ByD to SAP ByD, however, as far as I know, we didn’t migrate attachments and notes. In addition, we didn’t migrate history. Roughly: ending balances of GL Accounts, open items, depreciation, and master data – FAs, materials, services, accounts and so on. So, new system starts with empty history of Sales Order and Customer Invoices.

      Probably you will be interested in another post about mass download of data:

      Bulk extract data from SAP Business ByDesign &amp;#8211; Ivan Bond&amp;#039;s blog

      But again – only possible to extract fields of simple type, because method works with reporting engine.

      Kind regards,

      Ivan

      (0) 

Leave a Reply