Forum Discussion

JayWai's avatar
JayWai
New Member
3 years ago
Solved

Spreading rows based on division percentages from another table

Hi,  Sorry if this is handled elsewhere, I looked around but couldn't really find a good topic on what I'm looking for.      I'm looking for a way to duplicate and recalculate some data based on ...
Need Help
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    Given this table called 'Input2':

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lEyNVAFkm6lRXmZJaVFqUB2cGJOarFSrA66GpfU4sz0PBQFXql5eZlpqUVAQWOwGv+y1KKM1MQUINM3MS8xPTU3Na8EXakRppWlBQX5RRgK8bktJD+pEihgaIDP3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"% " = _t, BU = _t, Dept = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"% ", Percentage.Type}, {"BU", type text}, {"Dept", type text}})
in
    chgTypes

     

    You can create this table based on Input1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lFyK03NAVKmBkqxOgjRgMSi7My8dCDLCCLhlZqXl5mWWoTQYWSAIeOcWJJaBNFmCJUNyU+qROgBi8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Cost Type" = _t, Amount = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Cost Type", type text}, {"Amount", Int64.Type}}),
    mergeInput2 = Table.NestedJoin(chgTypes, {"Employee Name"}, Input2, {"Employee Name"}, "Input2", JoinKind.LeftOuter),
    expandInput2 = Table.ExpandTableColumn(mergeInput2, "Input2", {"% ", "BU", "Dept"}, {"% ", "BU", "Dept"}),
    addSplitAmount = Table.AddColumn(expandInput2, "splitAmount", each [Amount] * [#"% "]),
    remOthCols = Table.SelectColumns(addSplitAmount,{"Employee Name", "Cost Type", "BU", "Dept", "splitAmount"})
in
    remOthCols

     

    Output:

     

    I've attached the working PBIX below.

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi JayWai ,

 

You should just be able to merge Input2 to Input1 on [Employee Name], then multiply Input1[Amount] by Input2[%] to get the new amount column.

Remove any columns you no longer need.

 

If you can share some sample data of Input1 an Input2 in a copyable format, I can knock up a working query for you.

 

Pete

  • JayWai's avatar
    JayWai
    New Member
    3 years ago

    Hey Pete, 

     

    Thanks for your input. I copied the "data" attached. It's just a simplified and short version of what I'm actually working with. Just want to get the technique down... Probably relatively simple. 

     

    When merging, won't I just add one of the hits of Frank for example to his costs, instead of showing each line for Frank available? 

     

    Employee NameCost TypeAmount
    FrankFuel50
    FrankParking20
    JenniferFuel200
    JenniferCatering100
    TobyFuel100

     

    Employee Name% BUDept
    Frank50%FurnitureSales
    Frank50%DesignSales
    Jennifer30%OverheadManagement
    Jennifer20%FurnitureSupport
    Jennifer50%FurnitureSales
    Toby100%OverheadManagement

     

    Employee NameCost TypeAmountBUDept
    FrankFuel25FurnitureSales
    FrankFuel25DesignSales
    FrankParking10FurnitureSales
    FrankParking10DesignSales
    JenniferFuel60OverheadManagement
    JenniferFuel40FurnitureSupport
    JenniferFuel100FurnitureSales
    JenniferCatering30OverheadManagement
    JenniferCatering20FurnitureSupport
    JenniferCatering50FurnitureSales
    TobyFuel100OverheadManagement

     

    In any case thanks for your time and reply! 

     

    Kind regards, 

     

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      Given this table called 'Input2':

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lEyNVAFkm6lRXmZJaVFqUB2cGJOarFSrA66GpfU4sz0PBQFXql5eZlpqUVAQWOwGv+y1KKM1MQUINM3MS8xPTU3Na8EXakRppWlBQX5RRgK8bktJD+pEihgaIDP3lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"% " = _t, BU = _t, Dept = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"% ", Percentage.Type}, {"BU", type text}, {"Dept", type text}})
in
    chgTypes

       

      You can create this table based on Input1:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcitKzMtW0lFyK03NAVKmBkqxOgjRgMSi7My8dCDLCCLhlZqXl5mWWoTQYWSAIeOcWJJaBNFmCJUNyU+qROgBi8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Cost Type" = _t, Amount = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Cost Type", type text}, {"Amount", Int64.Type}}),
    mergeInput2 = Table.NestedJoin(chgTypes, {"Employee Name"}, Input2, {"Employee Name"}, "Input2", JoinKind.LeftOuter),
    expandInput2 = Table.ExpandTableColumn(mergeInput2, "Input2", {"% ", "BU", "Dept"}, {"% ", "BU", "Dept"}),
    addSplitAmount = Table.AddColumn(expandInput2, "splitAmount", each [Amount] * [#"% "]),
    remOthCols = Table.SelectColumns(addSplitAmount,{"Employee Name", "Cost Type", "BU", "Dept", "splitAmount"})
in
    remOthCols

       

      Output:

       

      I've attached the working PBIX below.

       

      Pete