Forum Discussion

THEG72's avatar
THEG72
Icon for Helper V rankHelper V
7 years ago
Solved

Calculated Column with numeric field and text field

I have the following table data and would like to make  a custom column that will have 2 possible outputs "Cash Inflow" or "Cash Outflow" as shown in the formula required section below.   There are...
Need Help
  • ChandeepChhabra's avatar
    ChandeepChhabra
    7 years ago

    THEG72 If my reply helped, could you mark it as a solution?

     

    thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Finance Group", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Finance Group]="Revenue" then if [Value]>0 then "Cash Inflow" else "Cash Outflow" else if [Value]<0 then "Cash Outflow" else "Cash Inflow")
in
    #"Added Custom"

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Finance Group", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Finance Group]="Revenue" then if [Value]>0 then "Cash Inflow" else "Cash Outflow" else if [Value]<0 then "Cash Outflow" else "Cash Inflow")
in
    #"Added Custom"

Hope this helps.

THEG72's avatar
THEG72
Icon for Helper V rankHelper V
7 years ago

Ashish_Mathur  thanks for your M code, what happens if the value is zero? how would you code account for that?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    What would you want the result to be?