Add calculated conditional Rows to existing table

[Ramiroz]

Hi,

I've a difficult challenge, whereby I need to transform a table by adding calculated rows, allocating 10% of revenue from Category 1 Projects to Category 2. And show this allocation as a separate line.

My data model includes a total of 5 categories (the example below just for illustration).

I want to transform the above original data by adding conditional row that allocates 10% revenue from each Category 1 Project towards Category 2. as below example:

So the final results using above transformed data will reflect this in a Matrix visual:

[Fowmy · Accepted solution]

@Ramiroz 

Please find below the Power Query Solution as desired, Copy the code below to a blank query and follow the steps, 

You can download the file: HERE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRck6E0CYGBnoGBkqxOghpIyRpQx0DTAXGUAUghUaY0iZI0oaY0qZIxhtjSpsh68Zmuzl+4y2QjDfDlLZEkjbHlDY0wGJ6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Category = _t, #"Project Value S" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Value S", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Category] = "Cat 1")),
    #"Inserted Multiplication" = Table.AddColumn(#"Filtered Rows", "Cat-1 Allocation", each [Project Value S] * 0.1, type number),
    Custom1 = Table.Combine({#"Changed Type",  Table.FromRecords({ [Project Name = "Allocation Cat 1", Category = "Cat 1", Project Value S = 
-List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]}), 
Table.FromRecords({ [Project Name = "Allocation Cat 2", Category = "Cat 2", Project Value S = 
List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]})
}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Project Value S", Int64.Type}})
in
    #"Changed Type1"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous · Accepted solution]

Hi @Ramiroz 

peraphs are you after somethink like this?

let
    ct = Table.TransformColumnTypes(source,{{"proj", type text}, {"cat", type text}, {"rev", Int64.Type}}),
    alloc = (pos)=>
    let 
    r=source[rev]{pos}
    in {source{pos}&[proj="c1 allocation",cat="cat1",rev=-r*0.1],source{pos}&[proj="c2 allocation",cat="cat2",rev=r*0.1]},

    pos=List.PositionOf(ct[cat],"c1",Occurrence.All),
    tt=List.Accumulate(pos,ct,(s,c)=>Table.InsertRows(s,c+List.PositionOf(pos,c)*2+1,alloc(c)))
in
    tt

[Anonymous]

let
    ct = Table.TransformColumnTypes(source,{{"proj", type text}, {"cat", type text}, {"rev", Int64.Type}}),
    alloc = (pos)=>
    let 
    r=source[rev]{pos}
    in {source{pos}&[rev=r*0.1],source{pos}&[rev=-r*0.1]},

    pos=List.PositionOf(ct[cat],"c1",Occurrence.All),
    tt=List.Accumulate(pos,ct,(s,c)=>Table.InsertRows(s,c+List.PositionOf(pos,c)*2+1,alloc(c)))
in
    tt

source  with lower case starting s  is a copy of your table.

this is the allocated table

[Fowmy]

@Ramiroz 

Do you need the solution in Power Query or Data Model (DAX) ?

Hope you have only 2 Categories? 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Ramiroz]

@Fowmy 

I want it in Power Query.

Unfortunately I have 5 categories, but only category 1 where I want to allocate 10% of it's revenue to category 2.

[Fowmy · Accepted solution]

@Ramiroz 

Please find below the Power Query Solution as desired, Copy the code below to a blank query and follow the steps, 

You can download the file: HERE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRck6E0CYGBnoGBkqxOghpIyRpQx0DTAXGUAUghUaY0iZI0oaY0qZIxhtjSpsh68Zmuzl+4y2QjDfDlLZEkjbHlDY0wGJ6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Category = _t, #"Project Value S" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Value S", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Category] = "Cat 1")),
    #"Inserted Multiplication" = Table.AddColumn(#"Filtered Rows", "Cat-1 Allocation", each [Project Value S] * 0.1, type number),
    Custom1 = Table.Combine({#"Changed Type",  Table.FromRecords({ [Project Name = "Allocation Cat 1", Category = "Cat 1", Project Value S = 
-List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]}), 
Table.FromRecords({ [Project Name = "Allocation Cat 2", Category = "Cat 2", Project Value S = 
List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]})
}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Project Value S", Int64.Type}})
in
    #"Changed Type1"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Ramiroz]

Thanks @Fowmy I tried it and it's perfect.

The only thing missing is that I don't want to sum/aggregate all the allocations of Cat1, I want to maintain it as a separate record for each project, under unique project name called "Cat1 Allocation", as I want to maintain other columns (ie.Date)

[Fowmy · Accepted solution]

@Ramiroz 

Check this solution, hope it is what you were expecting.

You can download the file: HERE

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous · Accepted solution]

Hi @Ramiroz 

peraphs are you after somethink like this?

let
    ct = Table.TransformColumnTypes(source,{{"proj", type text}, {"cat", type text}, {"rev", Int64.Type}}),
    alloc = (pos)=>
    let 
    r=source[rev]{pos}
    in {source{pos}&[proj="c1 allocation",cat="cat1",rev=-r*0.1],source{pos}&[proj="c2 allocation",cat="cat2",rev=r*0.1]},

    pos=List.PositionOf(ct[cat],"c1",Occurrence.All),
    tt=List.Accumulate(pos,ct,(s,c)=>Table.InsertRows(s,c+List.PositionOf(pos,c)*2+1,alloc(c)))
in
    tt