Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
For the table below, I wish to split the row into multiple rows but only for workdays. I've managed to split the rows with some research and fantastic assisrtance but still a little stuck. Any solution, help or guidance is much appreciated.
Thanks,
Solved! Go to Solution.
Hi @Anonymous ,
Using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJU0gEyzWFMMyA2BWJjEGFhoaoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, DurationDays = _t, Workdays = _t, Efforthrs = _t, Utilisation = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"DurationDays", Int64.Type}, {"Workdays", Int64.Type}, {"Efforthrs", Int64.Type}, {"Utilisation", Percentage.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start Date])..Number.From([End Date])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.From([Custom])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each if Date.DayOfWeek([Custom.1],Day.Monday)=5 or Date.DayOfWeek([Custom.1],Day.Monday)=6 then null else [Custom.1]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [DateList] <> null and [DateList] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1"})
in
#"Removed Columns"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
Does this mean you were not able to expand the rows?
If yes, there is other post with solution: https://community.powerbi.com/t5/Power-Query/Help-creating-an-academic-year-calendar-in-Power-Query/m-p/1936262#M57116
And then you can apply steps I mentioned earlier.
i can filter already to exclude Sat, Sun or Public Holiday as i have setup a calendar table. Issue is i lose the effort allocated on those days. I want to be able to set date to next working date..
Hi. Thank you. The issue is that i want to transfer the effort to next working day
Hi @Anonymous ,
If you already managed to create a new rows, you have most of the job done.
To keep only workdays you need to have a table, which would have column with Date and column with information, if there is a Public Holiday (I assume you want to exclude them as well).
Once you have these 2 columns in this table, add there a new column with Days of a Week (use Date.DayOfWeek).
As a last step in this table - add a conditional column, something like: If Days of Week contains Saturday or Sunday or Public Holiday is true, then give me "Not Working Day" else "Working Day".
Merge this table to your existing table, and apply filter only on "Working Day".
Let me know, if that helps!
Hi @Anonymous ,
Using below M codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJU0gEyzWFMMyA2BWJjEGFhoaoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, DurationDays = _t, Workdays = _t, Efforthrs = _t, Utilisation = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"DurationDays", Int64.Type}, {"Workdays", Int64.Type}, {"Efforthrs", Int64.Type}, {"Utilisation", Percentage.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start Date])..Number.From([End Date])}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.From([Custom])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each if Date.DayOfWeek([Custom.1],Day.Monday)=5 or Date.DayOfWeek([Custom.1],Day.Monday)=6 then null else [Custom.1]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [DateList] <> null and [DateList] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1"})
in
#"Removed Columns"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi Thank you so much. I already have an existing table with many opther columns not relevant to this calculation. Do i just change the table name to my table name as the other fields are the same? I'm only a true novice 🙂 Thank you
Hi @Anonymous ,
Yes,you could duplicate one then apply the calculation in the new table.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!