Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following Data in hand,
With above data in my hand, I would like to do the following
1. Divide the total Number of works to be done / Total Number of Weeks ( mentioned asWork in Numbers/ No.of Weeks on the Screenshot)
2. Allocate the Divided number of Works on the respective week numbers based on the Tenure of the Work
Week Number 1 - Represent Jan 2020 1st week (Jan 1- Jan 4)
Week Number 2 - Represent Jan 2020 2nd week (Jan 5- Jan 11)
Similarly the 5 weeks on the Month of January is given as ( 1 till 5 week splits of January on the Screenshot) .
Is this practically possible to implement this on PowerBI? If so Please provide some suggestions to get this done.
Thanks,
Govindaraj SV
Tasks | Work In Numbers | Start_Date | Due_Date | No.Of Weeks | 1 | 2 | 3 | 4 | 5 |
Task 1 | 360 | 1/1/2020 | 31/1/2020 | 5 | 72 | 72 | 72 | 72 | 72 |
Task 2 | 36 | 6/1/2020 | 26/01/2020 | 3 | 12 | 12 | 12 | ||
Task 3 | 60 | 13/1/2020 | 26/01/2020 | 2 | 30 | 30 | |||
Task 4 | 18 | 1/1/2020 | 18/01/2020 | 3 | 6 | 6 | 6 | ||
Task 5 | 160 | 17/1/2020 | 17/01/2020 | 3 | 53.34 | 53.34 | 53.34 |
Solved! Go to Solution.
@Anonymous
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCkkszi5WsgLTCoZKOkrh+UXZCp55Cn6luUmpRUApYzMDHaXgksSikniXxJJUoFojAyMDXQNDIAKqdylNRRc3NlSq1UEz2gi70bhMNsNuspEZpsnG2EzG6WZDY+JNNsFmsqEFiaFhaIFpsilWk3EHNA5HG5or1cYCAA==",BinaryEncoding.Base64),Compression.Deflate))),
chTypes = Table.TransformColumnTypes(Source,{{"Tasks", type text}, {"Work In Numbers", Int64.Type}, {"Start_Date", type date}, {"Due_Date", type date}},"en-GB"),
addRec = Table.AddColumn(
chTypes,
"t",
each let period = List.Transform({Date.WeekOfMonth([Start_Date])..Date.WeekOfMonth([Due_Date])}, Text.From),
cnt = List.Count(period),
rec = [NoOfWeek=cnt]&Record.FromList(List.Repeat({Number.RoundUp([Work In Numbers]/cnt,2)},cnt), period)
in rec
),
result = Table.ExpandRecordColumn(addRec, "t", {"NoOfWeek"}&{"1".."5"})
in
result
Is that what you want to achieve? If my code solves your problem, mark it as a solution
@Anonymous
#"addRec" = Table.AddColumn(#"Changed Type","t",each let period = List.Transform({Date.WeekOfYear([Start_date])..Date.WeekOfYear([Due_Date])}, Text.From),
cnt = List.Count(period),
rec = [NoOfWeek=cnt]&Record.FromList(List.Repeat({Number.RoundUp([Storypoints]/cnt,2)},cnt), period)
in rec
),
result = Table.ExpandRecordColumn(addRec, "t", {"NoOfWeek"}&List.Transform({1..52},Text.From))
@Anonymous
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCkkszi5WsgLTCoZKOkrh+UXZCp55Cn6luUmpRUApYzMDHaXgksSikniXxJJUoFojAyMDXQNDIAKqdylNRRc3NlSq1UEz2gi70bhMNsNuspEZpsnG2EzG6WZDY+JNNsFmsqEFiaFhaIFpsilWk3EHNA5HG5or1cYCAA==",BinaryEncoding.Base64),Compression.Deflate))),
chTypes = Table.TransformColumnTypes(Source,{{"Tasks", type text}, {"Work In Numbers", Int64.Type}, {"Start_Date", type date}, {"Due_Date", type date}},"en-GB"),
addRec = Table.AddColumn(
chTypes,
"t",
each let period = List.Transform({Date.WeekOfMonth([Start_Date])..Date.WeekOfMonth([Due_Date])}, Text.From),
cnt = List.Count(period),
rec = [NoOfWeek=cnt]&Record.FromList(List.Repeat({Number.RoundUp([Work In Numbers]/cnt,2)},cnt), period)
in rec
),
result = Table.ExpandRecordColumn(addRec, "t", {"NoOfWeek"}&{"1".."5"})
in
result
Is that what you want to achieve? If my code solves your problem, mark it as a solution
Tried the given suggestion, And I am ending upon the below error
@Anonymous
Modify it according to the picture above. If you can't correct it, send your code text and I will help you modify it
@ziying35 - Thank you so much for getting back. The suggestion is really working for me.
Can you please tell me , if the week numbers has to be on double digits, lets say for an year.
Week numbers would be from 1 to 52.
How to edit that value of week numbers. Directly changing the present end value of week number to 5 returns the following error.
Can you suggest how to handle this.
Expression.Error: The value isn't a single-character string.
Details:
Value=52
Thanks,
Govindaraj SV
@ziying35 , I tried exploring so i was able to get through what i need by making hte following Changes.
My changes are mentioned on Bold
#"addRec" = Table.AddColumn(#"Changed Type","t",each let period = List.Transform({Date.WeekOfYear([Start_date])..Date.WeekOfYear([Due_Date])}, Text.From),
cnt = List.Count(period),
rec = [NoOfWeek=cnt]&Record.FromList(List.Repeat({Number.RoundUp([Storypoints]/cnt,2)},cnt), period)
in rec
),
result = Table.ExpandRecordColumn(addRec, "t", {"NoOfWeek"}&{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52"})
And is there a way we can handle the above Bold Italic list to a better way? No.of Week to be Given in a different way thats more effective?
Thanks,
Govindaraj SV
@Anonymous
#"addRec" = Table.AddColumn(#"Changed Type","t",each let period = List.Transform({Date.WeekOfYear([Start_date])..Date.WeekOfYear([Due_Date])}, Text.From),
cnt = List.Count(period),
rec = [NoOfWeek=cnt]&Record.FromList(List.Repeat({Number.RoundUp([Storypoints]/cnt,2)},cnt), period)
in rec
),
result = Table.ExpandRecordColumn(addRec, "t", {"NoOfWeek"}&List.Transform({1..52},Text.From))
@ziying35 That did gave me what i wanted . Thanks a million. Closing the Threads by this as the need for the ask is now satisfied
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.