Dynamic Table Headers in Power Query (SAP ByDesign, Odata)

Another good example of how awesome is Power Query. And again I reference to one of well-known bloggers, Ken Puls’s post describes situation when we have pivoted data and need to unpivot them then rename columns. Fortunalety, in this example not so many columns used and unpivoting + join “rename table” is a solution.
However, in common case when we have table with 20 columns and 100 000 rows (or 2 million) unpivoting will be a very bad decision.
I faced with this challenge when I started to work with Odata protocol in SAP Business ByDesign.

Using Odata we have to provide string with list of fields ID in $select, for example query of Account Contact Data:

https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBPCSCONTB_Q0001QueryResults? $top=100000&$select=CBO_ROLE_CODE,CBP_STATUS_CODE,CBP_UUID,CCONT_FUNCTIONAL_AREA_CODE, CCONT_FUNCTION_TYPE_CODE,CCONT_MAIN_INDICATOR,CCONT_UUID,CCONT_WPA_BUILDING,CCONT_WPA_DEPARTMENT_NAME, CCONT_WPA_EMAIL_CONTENT,CCONT_WPA_FAX_NUMBER,CCONT_WPA_FLOOR,CCONT_WPA_FUNCTIONAL_TITLE, CCONT_WPA_MOBILE_NUMBER, CCONT_WPA_PHONE_NUMBER,CCONT_WPA_POSTAL_ADR_DESCR,CCONT_WPA_PREF_COMMU_MEDIUM, CCONT_WPA_ROOM_ID,CCURR_EMPL_RESP_PARTY_ROLE_CODE,CCURR_EMPL_RESP_UUID_CONTENT,CDEFADDR_FORM_POSTAL_ADDR, FK0COUNT, KK0COUNT,TBO_ROLE_CODE,TBP_STATUS_CODE,TBP_UUID,TCONT_FUNCTIONAL_AREA_CODE, TCONT_FUNCTION_TYPE_CODE, TCONT_UUID,TCONT_WPA_PREF_COMMU_MEDIUM,TCURR_EMPL_RESP_PARTY_ROLE_CODE, TCURR_EMPL_RESP_UUID_CONTENT& $format=json

or Invoice Volume query

$select=CITM_UUID,CDOC_UUID,CDPY_BUYER_UUID,CDPY_BILLTO_UUID,CDOC_CANC_IND,CDOC_CHANGED_DT, CDOC_CREATED_DT,CDOC_INTERCOMP_IND,CDOC_INV_DATE,TDOC_STA_RELEASE,TDOC_PROC_TYPE,CIPR_REFO_CATCP, CIPR_PROD_UUID,TIPR_REFO_PRDTY,CIBR_SLO_UUID,CIBR_SLO_ITM_UUID,CIBA_SAL_ORG,CIP_SALES_UNIT, KCITM_BA_QTY,KCITM_NET_AM_RC,RCITM_NET_AM_RC,KCITM_TX_AM_RC,Ts1ANs3C22103A3ABA41,Ts1ANs3ECC07DCEAB173, Cs1ANsDDD359BE95C3D4

In json response we have no names of fields, but work with IDs quite inconvenient. In case of extension fields simply impossible. Who can remember what stored in field Ts1ANs3C22103A3ABA41?
First idea that come in mind – rename all columns using $metadata of corresponding report.
Let’s consider formalized task.

Rename Columns Challenge

For column renaming opeartion in tables we have only

Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table

Example in documentation tell us

Table.RenameColumns(Table.FromRecords({[A=1, B=2]}), {{“A”, “B”},{“B”, “A”}})

so we need to get list of pairs “A->New_A_Name” for second argument.
Method of trials and errors brought me to formula

Table.Transpose(table as table, optional columns as any) as table

Which applied to “Rename Table” (Table2) gives table

A B C
Assassin Barbarian Crusader

then such table can be transformed by

Table.ToColumns(table as table) as list

// Returns a list of nested lists each representing a column of values in the input table.

Conclusion,
Table1 – initial table with data
Table2 – table containing list of IDs and Names

let
rename_list = Table.ToColumns(Table.Transpose(Table2)),
result = Table.RenameColumns(Table1, rename_list, MissingField.Ignore)
in
result

MissingField.Ignore is needed to avoid problems when somehow unexcpected column appeared in response.

Bonus:
M code to get IDs and Names of SAP ByDesign report. Just provide TenantId and ReportId to following function

names =
let
Source = OData.Feed(“https://my” & TenantId & “.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/$metadata?entityset=RP” & ReportId & “QueryResults”),
Content = Xml.Tables(Source),
DataServices = Content{0}[DataServices],
#”http://schemas microsoft com/ado/2008/09/edm” = DataServices{0}[#”http://schemas.microsoft.com/ado/2008/09/edm”%5D,
#”Expand Schema” = Table.ExpandTableColumn(#”http://schemas microsoft com/ado/2008/09/edm”, “Schema”, {“EntityType”}, {“Schema.EntityType”}),
#”Expand Schema.EntityType2″ = Table.ExpandTableColumn(#”Expand Schema”, “Schema.EntityType”, {“Property”}, {“Property”}),
#”Expand Property” = Table.ExpandTableColumn(#”Expand Schema.EntityType2″, “Property”, {“Attribute:Name”, “http://www.sap.com/Protocols/SAPData”}, {“Attribute:Name”, “http://www.sap.com/Protocols/SAPData”}),
#”Expand http://www.sap.com/Protocols/SAPData” = Table.ExpandTableColumn(#”Expand Property”, “http://www.sap.com/Protocols/SAPData”, {“Attribute:label”}, {“Attribute:label”}),
#”Renamed Columns” = Table.RenameColumns(#”Expand http://www.sap.com/Protocols/SAPData”,{{“Attribute:Name”, “Id”}, {“Attribute:label”, “Name”}}),
res = Table.SelectRows(#”Renamed Columns”, each ([Id] “ID” and [Id] “TotaledProperties”))
in
res

Advertisements

8 thoughts on “Dynamic Table Headers in Power Query (SAP ByDesign, Odata)

  1. Ivan Bondarenko 2015-10-07 / 23:09

    In Bonus part I would change “OData.Feed” on “Web.Contents”, as I noticed that Web.Contents works faster.

  2. Emanuel 2016-08-05 / 19:48

    Hey thanks a lot for that approach. I have been looking for a way to do this for a while. I am however running into some problems and I was wondering if you knew how to solve this. When I try your approach I always get this error message:

    Expression.Error: We expected a RenameOperations value.
    Details:
    List

    • Ivan Bondarenko 2016-08-05 / 20:02

      Hi Emanuel, thanks for contact me and for usage my advice. Could you please share piece of your code and line where you get error? I don’t remember that I saw such issue. Usually in Power Query even small mistake can lead to error, developers have to be very accurate.
      And out of curiosity, do you also use SAP ByD?
      Thanks, Ivan

  3. Jeff 2017-03-13 / 16:11

    Ivan Thank you. This helped me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s