Forum Discussion
How to turn one row into multiple rows based on calculation
I'm attempting to solve an issue of distrubuting management services fees using Power BI.
The model for distribution is based on hours used on the spesific customers, and if the customer is properly identified in the time sheet, this works fine.
However, there is currently the possibility to register the used hours on a general management category. These hours should also be included in the distribution to the end users. The split would have to be calculated based on a set of different allocation keys (e.g. number of employees, number of users).
Here is an attempt to illustrate the problem, and what I hope to accomplish:
I hope someone can help:)
kjetiljy , here's a solution based on your sample data. You need to tweak the code according to you senario.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MS0xPzU3NK1HSUbJUitWJVnIuLS7Jz00tUjAECpmiChkBhUxQhYyBQsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, #"Used Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"Used Hours", Int64.Type}}), #"Split Hours" = let col= #"Changed Type"[Used Hours] in {0} & List.Transform(List.Skip(col), each _ + col{0}/(List.Count(col)-1)), #"Combined Columns" = Table.FromColumns({#"Changed Type"[Activity], #"Split Hours"}, {"Activity", "Split Hours"}) in #"Combined Columns"
2 Replies
kjetiljy , here's a solution based on your sample data. You need to tweak the code according to you senario.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MS0xPzU3NK1HSUbJUitWJVnIuLS7Jz00tUjAECpmiChkBhUxQhYyBQsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, #"Used Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"Used Hours", Int64.Type}}), #"Split Hours" = let col= #"Changed Type"[Used Hours] in {0} & List.Transform(List.Skip(col), each _ + col{0}/(List.Count(col)-1)), #"Combined Columns" = Table.FromColumns({#"Changed Type"[Activity], #"Split Hours"}, {"Activity", "Split Hours"}) in #"Combined Columns"let Source = Table.FromRows( { {"Management", 9, -1}, {"Customer1", 5, 1/3}, {"Customer2", 4, 1/3}, {"Customer3", 3, 1/3} }, type table [Activity = text, Used Hours = number, Allocation = number] ), #"Added Custom" = Table.AddColumn(Source, "Reallocation", each Source{0}[Used Hours] * [Allocation] + [Used Hours]) in #"Added Custom"
