none
Custom Column Value Depending on from other column value RRS feed

  • Question

  • morning

    I am trying to have Custom Column Value depending from others column. For each line I need that my Customn 1 is egal to the sales of the country by brand

    i have try several option but I stuck on this issue

    Example of the final result below

    Country Brand Account Value Customn1
    Country A Brand A Sales 100 0
    Country A Brand A Cost 40 100
    Country A Brand A Expenses 30 100
    Country A Brand B Sales 120 0
    Country A Brand B Cost 30 120
    Country A Brand B Expenses 50 120
    Country G Brand A Sales 50 0
    Country G Brand A Cost 0 50
    Country G Brand A Expenses 30 50
    Country G Brand B Sales 150 0
    Country G Brand B Cost 70 150
    Country G Brand B Expenses 10 150

    Thanks for your help

    Regards

    Arnaud


    Thursday, April 13, 2017 2:58 PM

Answers

  • Here's another approach that uses a Conditional Column, followed by a Fill Down operation.

    let
        Source = ...,
        #"Added Conditional Column" = Table.AddColumn(Source, "Sales", each if [Account] = "Sales" then [Value] else null ),
        #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Sales"})
    in
        #"Filled Down"

    Ehren

    Friday, April 14, 2017 9:20 PM
    Owner
  • Hi Arnaud,

    Here's one option, which involves doing a "group by" operation, pulling out the Sales, and then re-expanding the Account and Value.

    let
        Source = ...,
        #"Grouped Rows" = Table.Group(Source, {"Country", "Brand"}, {{"Rows", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sales", each Table.SelectRows([Rows], each [Account] = "Sales"){0}[Value]),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Account", "Value"}, {"Account", "Value"})
    in
        #"Expanded Rows"

    Ehren

    Friday, April 14, 2017 9:18 PM
    Owner

All replies

  • Hi Arnaud,

    Here's one option, which involves doing a "group by" operation, pulling out the Sales, and then re-expanding the Account and Value.

    let
        Source = ...,
        #"Grouped Rows" = Table.Group(Source, {"Country", "Brand"}, {{"Rows", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sales", each Table.SelectRows([Rows], each [Account] = "Sales"){0}[Value]),
        #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Account", "Value"}, {"Account", "Value"})
    in
        #"Expanded Rows"

    Ehren

    Friday, April 14, 2017 9:18 PM
    Owner
  • Here's another approach that uses a Conditional Column, followed by a Fill Down operation.

    let
        Source = ...,
        #"Added Conditional Column" = Table.AddColumn(Source, "Sales", each if [Account] = "Sales" then [Value] else null ),
        #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Sales"})
    in
        #"Filled Down"

    Ehren

    Friday, April 14, 2017 9:20 PM
    Owner