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

[kenven01]

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.

[v-tangjie-msft]

Hi @kenven01 ,

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. 

[kenven01]

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.

[v-tangjie-msft]

Hi @kenven01 ,

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. 

[kenven01]

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)

[kenven01]

Please see the hot screenshot.

[v-tangjie-msft]

Hi @kenven01 ,

As you can see from your screenshot the month column of the month table is listed as "Month Level 01" and we need to write the original column name in square brackets []. Modify your calculated column as shown below.

Please note that you will need to select the month table and add this calculated column to the month table.

New month = 
var _a= RIGHT([Month Level 01],4) 
var _b=SWITCH(TRUE(),
CONTAINSSTRING([Month Level 01],"JAN"),"01",
CONTAINSSTRING([Month Level 01],"FEB"),"02",
CONTAINSSTRING([Month Level 01],"MAR"),"03",
CONTAINSSTRING([Month Level 01],"APR"),"04",
CONTAINSSTRING([Month Level 01],"MAY"),"05",
CONTAINSSTRING([Month Level 01],"JUN"),"06",
CONTAINSSTRING([Month Level 01],"JUL"),"07",
CONTAINSSTRING([Month Level 01],"AUG"),"08",
CONTAINSSTRING([Month Level 01],"SEP"),"09",
CONTAINSSTRING([Month Level 01],"OCT"),"10",
CONTAINSSTRING([Month Level 01],"NOV"),"11",
CONTAINSSTRING([Month Level 01],"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. 

[kenven01]

Hi Neeko,

     The screenshot for the previoulsy memtioned error message below.

[v-tangjie-msft]

Hi @kenven01 ,

(1)Select Month table.

(2)Create a column.

New month column = 
var _a= RIGHT([Month Level 01],4) 
var _b=SWITCH(TRUE(),
CONTAINSSTRING([Month Level 01],"JAN"),"01",
CONTAINSSTRING([Month Level 01],"FEB"),"02",
CONTAINSSTRING([Month Level 01],"MAR"),"03",
CONTAINSSTRING([Month Level 01],"APR"),"04",
CONTAINSSTRING([Month Level 01],"MAY"),"05",
CONTAINSSTRING([Month Level 01],"JUN"),"06",
CONTAINSSTRING([Month Level 01],"JUL"),"07",
CONTAINSSTRING([Month Level 01],"AUG"),"08",
CONTAINSSTRING([Month Level 01],"SEP"),"09",
CONTAINSSTRING([Month Level 01],"OCT"),"10",
CONTAINSSTRING([Month Level 01],"NOV"),"11",
CONTAINSSTRING([Month Level 01],"DEC"),"12")
RETURN INT(_a & _b)

If you want to create a measure, use the following formula.

New month measure = 
var _a= RIGHT(MAX('Month'[Month Level 01]),4) 
var _b=SWITCH(TRUE(),
CONTAINSSTRING(MAX('Month'[Month Level 01]),"JAN"),"01",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"FEB"),"02",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"MAR"),"03",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"APR"),"04",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"MAY"),"05",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"JUN"),"06",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"JUL"),"07",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"AUG"),"08",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"SEP"),"09",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"OCT"),"10",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"NOV"),"11",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"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. 

[kenven01]

Hi Neeko,

    As I menetioned earlier, new column function is greyed out, so no chance for my case to new a column. That's a tough issue for data source frm SAP query.

   See the screenshot below.

[v-tangjie-msft]

Hi @kenven01 ,

Please try to create a measure.

New month measure = 
var _a= RIGHT(MAX('Month'[Month Level 01]),4) 
var _b=SWITCH(TRUE(),
CONTAINSSTRING(MAX('Month'[Month Level 01]),"JAN"),"01",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"FEB"),"02",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"MAR"),"03",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"APR"),"04",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"MAY"),"05",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"JUN"),"06",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"JUL"),"07",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"AUG"),"08",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"SEP"),"09",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"OCT"),"10",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"NOV"),"11",
CONTAINSSTRING(MAX('Month'[Month Level 01]),"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. 

[kenven01]

Hi Neeko,

    Good day.   

    It seems to have data load problem this time.

    Please see the screenshot below.

[v-tangjie-msft]

Hi @kenven01 ,

Please try to add the month columns displayed as numbers in the data source or connect to the data source in import mode instead.

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. 

[kenven01]

Hi Neeko,

    Nornally we will design PBI report based on queries built on cube(s) from SAP BW.

    Cube data is often seen as the raw data of report, we design query to meet the needs of            report something like  ffiltering, row/column format, caculation formula.

    So, we won't create visualization reports based on cube data directly.

    Other than date(month) issue like the case, other characteristics like customer,material with        key id and text value , key id can not displayed properly.

    Even though I tried loading data with method of 'Import' mode,

    error is still there.

    See the screenshot below.

[v-tangjie-msft]

Hi @kenven01 ,

If you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

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. 

[kenven01]

Hi Neeko,

     I've got it.

     Thank your for your great help.

     Kenven01

[kenven01]

Hi Neeko, It still doesn't work with error 'The value for 'Month Level 01' cannot be determined. Either the column doesn't exist, or there is no current row for this column'. Regards, Kenven

[kenven01]

Hi Neeko, It still doesn't work with error 'The value for 'Month Level 01' cannot be determined. Either the column doesn't exist, or there is no current row for this column'. Regards, Kenven