Hi experts, I have SAP BW (7.3) Bex Query as a data source for my BI desktop report. However, the keys like month, plant and materials number are can only be displayed as text instead of key ID. For example, data field MONTH(0calmonth in BW) is displed as 'JAN 2024' , finding no way to display as '202401'. Actually both '202401' and 'JAN 2024' can be displayed in BW query. Anyone can help with this ? Thank you in advance.

Hi Anonymous , According to your description, here are my steps you can follow as a solution. (1)Click "transform data" to enter power query --> split Month column by space. (2)Add conditional columns. (3)Merge the columns [Month.2], [Custom.1], [Custom]. Note that columns are selected in merge order. -->Modifies the data type of the column. Modify the merge column name and delete the [Month.1] column and then the result is as follows. Best Regards, Neeko Tang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Neeko, Firstly thank you for your quick response. I also tried to do the same things as you post, however, the transformation data function is disabled/greyed out for my case, which the data soucre is coming from SAP BW query thru SAP connector (ver 3.0) and using DirectQuery rather than import method to open data. Only report view is there, no table and model view for my case. So, I'm wondering some PBI features on data navigation are restricted for SAP BW query. I already use the latest PBI desttop version that is released in this month.

Hi Anonymous , Please try creating a calculated column. New month = var _a= RIGHT([Month],4) var _b=SWITCH(TRUE(), CONTAINSSTRING([Month],"JAN"),"01", CONTAINSSTRING([Month],"FEB"),"02", CONTAINSSTRING([Month],"MAR"),"03", CONTAINSSTRING([Month],"APR"),"04", CONTAINSSTRING([Month],"MAY"),"05", CONTAINSSTRING([Month],"JUN"),"06", CONTAINSSTRING([Month],"JUL"),"07", CONTAINSSTRING([Month],"AUG"),"08", CONTAINSSTRING([Month],"SEP"),"09", CONTAINSSTRING([Month],"OCT"),"10", CONTAINSSTRING([Month],"NOV"),"11", CONTAINSSTRING([Month],"DEC"),"12") RETURN INT(_a & _b) Best Regards, Neeko Tang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Neeko, The data field 'Month' in query is viewed as text table field, not key figures. So I have to change '[Month]' to '(Month)' in DAX codes as below. But it prompts me with error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'. So the way still doesn't work.--------- Codes ---------------------------------------------New month =var _a= RIGHT((Month),4)var _b=SWITCH(TRUE(),CONTAINSSTRING((Month),"JAN"),"01",CONTAINSSTRING((Month),"FEB"),"02",CONTAINSSTRING((Month),"MAR"),"03",CONTAINSSTRING((Month),"APR"),"04",CONTAINSSTRING((Month),"MAY"),"05",CONTAINSSTRING((Month),"JUN"),"06",CONTAINSSTRING((Month),"JUL"),"07",CONTAINSSTRING((Month),"AUG"),"08",CONTAINSSTRING((Month),"SEP"),"09",CONTAINSSTRING((Month),"OCT"),"10",CONTAINSSTRING((Month),"NOV"),"11",CONTAINSSTRING((Month),"DEC"),"12")RETURN INT(_a & _b)

How to get key ID from instead text from SAP BW query.

17 Replies

Anonymous
Not applicable
2 years ago
Hi Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1)Click "transform data" to enter power query --> split Month column by space.

(2)Add conditional columns.

(3)Merge the columns [Month.2], [Custom.1], [Custom]. Note that columns are selected in merge order. -->Modifies the data type of the column.

Modify the merge column name and delete the [Month.1] column and then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.
How to get key ID from instead text from SAP BW query.pbix28 KB
- Anonymous
  Not applicable
  2 years ago
  Hi Neeko,
  Firstly thank you for your quick response.
  I also tried to do the same things as you post, however,
  the transformation data function is disabled/greyed out for my case, which the data soucre is coming
  from SAP BW query thru SAP connector (ver 3.0) and using DirectQuery rather than import method to
  open data. Only report view is there, no table and model view for my case.
  So, I'm wondering some PBI features on data navigation are restricted for SAP BW query.
  I already use the latest PBI desttop version that is released in this month.
  - Anonymous
    Not applicable
    2 years ago
    Hi Anonymous ,
    
    Please try creating a calculated column.
    
    New month = var _a= RIGHT([Month],4) var _b=SWITCH(TRUE(), CONTAINSSTRING([Month],"JAN"),"01", CONTAINSSTRING([Month],"FEB"),"02", CONTAINSSTRING([Month],"MAR"),"03", CONTAINSSTRING([Month],"APR"),"04", CONTAINSSTRING([Month],"MAY"),"05", CONTAINSSTRING([Month],"JUN"),"06", CONTAINSSTRING([Month],"JUL"),"07", CONTAINSSTRING([Month],"AUG"),"08", CONTAINSSTRING([Month],"SEP"),"09", CONTAINSSTRING([Month],"OCT"),"10", CONTAINSSTRING([Month],"NOV"),"11", CONTAINSSTRING([Month],"DEC"),"12") RETURN INT(_a & _b)
    
    Best Regards,
    
    Neeko Tang
    
    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    How to get key ID from instead text from SAP BW query.pbix29 KB