How to have bracket for negative values from SAP BW Queries

[ljilibert]

Hi All,

When I load my SAP BW Queries in PowerBI, I loose the bracket for negative values. In my PowerBI source, I have no negative values but have some in SAP BW queries.

Can people help me to load complete data with bracket for negative values ?

Regards,

Laurent J.

[petergmk]

Hi Lydia,

I experince the same issue with the negative signs (-) in BW (BEx) queries become lost when imported into Power BI, though the negative values appear intact when viewed in Excel. The signage is needed to aggregate values of multiple records extracted from BW correctly during report run time so it cannot be determined and formatted beforehand, as there is no way we can tell when the values should be negative or positive. The issue can stop potential customers on BW from considering and leveraging Power BI for reporting and analysis purposes. Is there a way to bring this up to the development team if it is a real bug?

Thank you.

Regards,

Peter

[tristanstcyr]

Hi,

Measure values should be returned as numbers. What query are you using for importing the data? You can find this by going:

- PBI Desktop > Ribbon > Edit Queries

- Query Editor > View > Advanced Editor.

Thanks,

Tristan

[ljilibert]

Hi,

When using BW queries, advanced editor is containing source code as followed:

let
    Source = SapBusinessWarehouse.Cubes("", "", "", [LanguageCode="EN"]),
    DPA19MCR = Source{[Name="src_name"]}[Data],
    #"query_name" = src_name{[Id="src_name/query_name"]}[Data],
    #"Added Items" = Cube.Transform(#"query_name", {{Cube.AddMeasureColumn, "field_measure", "[Measures].[00O2TIJFJX0OAX63QWHX6DAJ2]"}}

in

#

So we can't specify the type of our measure. Data are arriving in "near real" number format and we are forced to change type as decimal number for being able to work with.

[v-yuezhe-msft]

Hi @ljilibert,

I suspect that Power BI Desktop cannot properly recognize the brackets for negative vaules. But after importing data from SAP BW to Desktop, you can display the negative values with desired format with manual operation.

Which format do you want to display the negative values as? For example, if you want to display -200 to (200) in Power BI, you can manually create a calculated column and format your original column containing the negative values by using the formula below.

New Column  = if(Table1[Column1]< 0, "("& ABS(Table1[Column1]) & ")", Table1[Column1]&"")

However, if you want to display -200 to (-200) in Power BI, please use the following formula.

New Column1  = if(Table1[Column1]< 0, "("& Table1[Column1])& ")", Table1[Column1]&"")

There is an example for your reference, for more details, you can review the attached PBIX file.


Thanks,
Lydia Zhang

[ljilibert]

Thanks for your answer Lydia. In fact, my problem is more complex.

When I execute my query in bex analyzer or in excel I have negative values. But when I import the same query in PowerBI, there's no negative values as if BW connector was moving all values in absolute.

So I can't test the value for checking if it's positive or negative.

For your side, how can I import negative values from a bw query into powerbi because It was available when bw connector was in beta.

Regards,

Laurent J.