Forum Discussion

RAKESH1986's avatar
RAKESH1986
Helper II
3 years ago
Solved

two column subtraction, first value is not correct

Hi All,

pls help me to get subtrction of two column.

 

i have these two column..

 

 

after this i added two index clumn and merge of these two index column 

 

 

my subtraction is coming correctly but first value it wrong it should be 0.53%..

first value should not be null, this must take first value of source column

 

pls help me to solve this issue, i am new here so need simple solution..

 

let
Source = #"15_EARLY_PW_BASELINE",
#"Removed Columns" = Table.RemoveColumns(Source,{"Discipline"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"EARLY_PW_WK"}, {{"PW_EARLY_CUM", each List.Sum([PW_EARLY_BASELINE]), type nullable number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"PW_EARLY_CUM", Percentage.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"EARLY_PW_WK", "PW_EARLY_CUM", "Index", "Index.1"}, {"EARLY_PW_WK.1", "PW_EARLY_CUM.1", "Index.2", "Index.1.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
#"Inserted Subtraction" = Table.AddColumn(#"Sorted Rows", "Subtraction", each [PW_EARLY_CUM] - [PW_EARLY_CUM.1], Percentage.Type)
in
#"Inserted Subtraction"

 

 

thanks

rakesh

  • RAKESH1986 

    >> Select column [PW_EARLY_CUM.1] >> Click Right Click >> Go to replace value >> Null to 0 >> Get Your Desired Output.

    Thank You!
    **Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

  • Mahesh0016's avatar
    Mahesh0016
    3 years ago

    RAKESH1986 

    >>First Remove This Step before replase value>>#"Inserted Subtraction" = Table.AddColumn(#"Sorted Rows", "Subtraction", each [PW_EARLY_CUM] - [PW_EARLY_CUM.1] >> 

7 Replies

  • RAKESH1986 

    >> Select column [PW_EARLY_CUM.1] >> Click Right Click >> Go to replace value >> Null to 0 >> Get Your Desired Output.

    Thank You!
    **Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

    • Mahesh0016's avatar
      Mahesh0016
      Super User

      RAKESH1986 

      >>First Remove This Step before replase value>>#"Inserted Subtraction" = Table.AddColumn(#"Sorted Rows", "Subtraction", each [PW_EARLY_CUM] - [PW_EARLY_CUM.1] >> 

  • RAKESH1986 
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • RAKESH1986's avatar
      RAKESH1986
      Helper II

      Mahesh0016 

       

      this data i have 

      have two column , need the resulf in third column

       

      pw01 = 0.53

      pw02= 1.61-0.53

      pw03=2.55-1.61

       

      need output
      EARLY_PW_WK, PW_EARLY_CUM, Weekly
      PW01, 0.53% ,0.53%
      PW02 ,1.61%, 1.07%
      PW03, 2.55% ,0.95%
      PW04 ,2.89%, 0.33%
      PW05, 3.44%, 0.56%
      PW06 ,4.44% ,1.00%

      • Mahesh0016's avatar
        Mahesh0016
        Super User

        RAKESH1986 

        *i hope this post help you.
        **If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.