Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Ramiroz
Frequent Visitor

Add calculated conditional Rows to existing table

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).

 

Ramiroz_0-1599301647503.png

 

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:

 

Ramiroz_1-1599301673587.png

 

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

Ramiroz_2-1599301733272.png

3 ACCEPTED SOLUTIONS

@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

 

Fowmy_0-1599305992165.png

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Anonymous
Not applicable

image.pngHi @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

 

 

 

 

 

 

View solution in original post

@Ramiroz 

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

You can download the file: HERE



Fowmy_0-1599331329051.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

 

 

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 is a copy of your table.

 

 

image.png

 

this is the allocated table

 

image.png

Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ramiroz
Frequent Visitor

@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.

@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

 

Fowmy_0-1599305992165.png

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ramiroz
Frequent Visitor

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)

 

Ramiroz_1-1599318958145.png

 

 

 

@Ramiroz 

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

You can download the file: HERE



Fowmy_0-1599331329051.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

image.pngHi @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

 

 

 

 

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.