Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.