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.

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

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”],
#”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