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

Hi Anonymous ,

(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. 

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.

Hi Anonymous ,

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. 

Hi Neeko,

    Good day.   

    It seems to have data load problem this time.

    Please see the screenshot below.

Hi Anonymous ,

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. 

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.

Hi Anonymous ,

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. 

Hi Neeko,

     I've got it.

     Thank your for your great help.

     Kenven01