Absolute Value of data

[xenon325i]

Hi all, first post here

I'm trying to show some sales data but because my data comes from an invoice table the values are all negative which doesn't look nice in my report. I'm using Direct Query as I need this data to be as live (ish) as possible. How can I change these values to be positive (ABS Value)?

I tried to click on the column header to change but it tells me I have to swap to import mode and I don't really want to do that. I tried to put it directly in the Query Editor using "Select ABS([Amount]) FROM...." hoping PowerBI wouldn't notice but it did, and again told me to switch to import mode to do this.

Is there any way to change the sign of this data in Direct Query Mode?

Thanks!

[v-yuezhe-msft]

@xenon325i,

You can create a custom column in Query Editor of Power BI Desktop using the code below.

Number.Abs([Amount])

There is an example for your reference.


Regards,
Lydia

[xenon325i]

Thanks Lydia but I already tried that - you can't appear to add a custom column in Direct Query mode:

[v-yuezhe-msft]

@xenon325i,

Do you connect to SQL Server database? What is the data type of  the Amount column in SQL table?

Regards,
Lydia

[xenon325i]

Yes it's a connection to SQL and the data type is decimal number. As you can see, the ABS value is correct in the Custom column but as soon as I try to apply my changes I get the message that I must be in Import Mode.

[v-yuezhe-msft]

@xenon325i,

Do you use Power BI Desktop August version(2.61.5192.601)? I am unable to reproduce your issue here, I create a Amount column in SQL table with decimal(18,2) data type.

Regards,
Lydia

[xenon325i]

Yes that is the version I am using. This is my first ever BI report so I'm probably doing something wrong because it seems I cannot do any kind of data transformation or add custom coulmns or anything at all unless I switch to import mode where it works perfectly.

[v-yuezhe-msft]

@xenon325i,

You can create a calculated column instead as shown in the following screenshot.



Or you can add a new column which has positive values in the SQL table, then import the SQL table in Power BI Desktop using DirectQuery mode.

Regards,
Lydia

[xenon325i]

The Calculated column almost works but for some reason the value (even though it is now positive) is slightly different from the original negative value. I'm not sure why that is but it's a start.

Appreciate your help!

It would be nice to know why my basic transformation doesn't work though - from what I've read on-line, it should...

[v-yuezhe-msft]

@xenon325i，

Please post the DDL script you use to create SQL table , and share sample data of your table here so that I can test.

Regards,
Lydia

[xenon325i]

I'm not sure what DDL script is - do you mean this?:

let
    Source = Sql.Database("ERPServer", "UKLive", [Query="select [Amount],[Posting Date] FROM [Company Live$G_L Entry] WHERE [G_L Account No_] BETWEEN 100030 and 111990#(lf)"])
in
    Source

[v-yuezhe-msft]

@xenon325i,

Right-click  your table in SSMS, then get create statement using option below. Also please share sample data of you table in Excel and share Excel to me.



Regards,
Lydia

[xenon325i]

OK this is to create the table:

/****** Object:  Table [dbo].[Company Live$G_L Entry]    Script Date: 17/08/2018 10:49:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Company Live$G_L Entry](
 [timestamp] [timestamp] NOT NULL,
 [Entry No_] [int] NOT NULL,
 [G_L Account No_] [varchar](20) NOT NULL,
 [Posting Date] [datetime] NOT NULL,
 [Document Type] [int] NOT NULL,
 [Document No_] [varchar](20) NOT NULL,
 [Description] [varchar](50) NOT NULL,
 [Bal_ Account No_] [varchar](20) NOT NULL,
 [Amount] [decimal](38, 20) NOT NULL,
 [Global Dimension 1 Code] [varchar](20) NOT NULL,
 [Global Dimension 2 Code] [varchar](20) NOT NULL,
 [User ID] [varchar](20) NOT NULL,
 [Source Code] [varchar](10) NOT NULL,
 [System-Created Entry] [tinyint] NOT NULL,
 [Prior-Year Entry] [tinyint] NOT NULL,
 [Job No_] [varchar](20) NOT NULL,
 [Quantity] [decimal](38, 20) NOT NULL,
 [VAT Amount] [decimal](38, 20) NOT NULL,
 [Business Unit Code] [varchar](10) NOT NULL,
 [Journal Batch Name] [varchar](10) NOT NULL,
 [Reason Code] [varchar](10) NOT NULL,
 [Gen_ Posting Type] [int] NOT NULL,
 [Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
 [Gen_ Prod_ Posting Group] [varchar](10) NOT NULL,
 [Bal_ Account Type] [int] NOT NULL,
 [Transaction No_] [int] NOT NULL,
 [Debit Amount] [decimal](38, 20) NOT NULL,
 [Credit Amount] [decimal](38, 20) NOT NULL,
 [Document Date] [datetime] NOT NULL,
 [External Document No_] [varchar](20) NOT NULL,
 [Source Type] [int] NOT NULL,
 [Source No_] [varchar](20) NOT NULL,
 [No_ Series] [varchar](10) NOT NULL,
 [Tax Area Code] [varchar](20) NOT NULL,
 [Tax Liable] [tinyint] NOT NULL,
 [Tax Group Code] [varchar](10) NOT NULL,
 [Use Tax] [tinyint] NOT NULL,
 [VAT Bus_ Posting Group] [varchar](10) NOT NULL,
 [VAT Prod_ Posting Group] [varchar](10) NOT NULL,
 [Additional-Currency Amount] [decimal](38, 20) NOT NULL,
 [Add_-Currency Debit Amount] [decimal](38, 20) NOT NULL,
 [Add_-Currency Credit Amount] [decimal](38, 20) NOT NULL,
 [Close Income Statement Dim_ ID] [int] NOT NULL,
 [IC Partner Code] [varchar](20) NOT NULL,
 [Reversed] [tinyint] NOT NULL,
 [Reversed by Entry No_] [int] NOT NULL,
 [Reversed Entry No_] [int] NOT NULL,
 [Prod_ Order No_] [varchar](20) NOT NULL,
 [FA Entry Type] [int] NOT NULL,
 [FA Entry No_] [int] NOT NULL,
 [Audited By] [varchar](20) NOT NULL,
 [C_A_R_ Line Type] [int] NOT NULL,
 [C_A_R_ Line No_] [varchar](20) NOT NULL,
 [C_A_R_ Line Variant] [varchar](10) NOT NULL,
 [C_A_R_ Line Quantity] [decimal](38, 20) NOT NULL,
 [C_A_R_ Line Inv_ Posting Group] [varchar](20) NOT NULL,
 CONSTRAINT [Company Live$G_L Entry$0] PRIMARY KEY CLUSTERED
(
 [Entry No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
) ON [Data Filegroup 1]

GO

SET ANSI_PADDING OFF
GO

What's the best way to send you the Excel sample?

[v-yuezhe-msft]

@xenon325i,

You can upload Excel to OneDrive or Dropbox and then post shared link of the file here.

Regards,
Lydia

[xenon325i]

Here is a sample:

https://sunhydraulics-my.sharepoint.com/:x:/p/ian_callow/ERpCHAEzpNxBnYhcZtPbvDYBzeVVliY6gKipiqL7Oag...

[v-yuezhe-msft]

@xenon325i,

I am unable to access the above link as it requires permission.


Regards,
Lydia

[xenon325i]

Sorry, try this:

https://sunhydraulics-my.sharepoint.com/:x:/p/ian_callow/ERpCHAEzpNxBnYhcZtPbvDYBzeVVliY6gKipiqL7Oag...

[v-yuezhe-msft]

@xenon325i,

I can't find any Amount column in your Excel file, and I am unable to reproduce this issue when creating a table using your SQL script.

I would recommend you add a column in the SQL data source. 

Regards,
Lydia

[xenon325i · Accepted solution]

Sorry I sent the wrong sample

I will use your work-around by adding a column at source.

Thank you for your help.