Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello,
I have a table with a capacity of kind of work per employee.
EmployeeName | KindOfWork | Capacity | FromDate | ToDate |
Bob | Sales | 0.8 | 01.01.2020 | 31.08.2020 |
Bob | Technical | 0.2 | 01.01.2020 | 31.08.2020 |
Bob | Sales | 0.9 | 01.09.2021 | 31.12.2021 |
Bob | Technical | 0.1 | 01.09.2021 | 31.12.2021 |
Paul | Sales | 1 | 01.01.2020 | 31.12.2021 |
I want to create a graph who show me the variation of the capacity month by month per KindOfWork.
How can I create this ? I need (I think), a table of each month (or day) between FromDate and ToDate, but I don't know how to create that.
Any idea? Thanks a lot for your help.
Solved! Go to Solution.
Hi @xdumont ,
Please refer to my pbix file to see if it helps you.
A table of each or day between FromDate and ToDate.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRCk7MSS0G0gZ6FkDSyMDIQN9Q3xDGtNA3NlSK1YEpDklNzsjLTE7MAWswIqwBYbolRIWhviVMMVCXET7jDfHqCEgszUGywBDDLXDFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeName = _t, KindOfWork = _t, Capacity = _t, FromDate = _t, ToDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeName", type text}, {"KindOfWork", type text}, {"Capacity", type number}, {"FromDate", type date}, {"ToDate", type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"ToDate", type date}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([ToDate] - [FromDate]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Custom", each List.Dates([FromDate],[Subtraction],#duration(1,0,0,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @xdumont ,
Please refer to my pbix file to see if it helps you.
A table of each or day between FromDate and ToDate.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRCk7MSS0G0gZ6FkDSyMDIQN9Q3xDGtNA3NlSK1YEpDklNzsjLTE7MAWswIqwBYbolRIWhviVMMVCXET7jDfHqCEgszUGywBDDLXDFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeName = _t, KindOfWork = _t, Capacity = _t, FromDate = _t, ToDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EmployeeName", type text}, {"KindOfWork", type text}, {"Capacity", type number}, {"FromDate", type date}, {"ToDate", type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"ToDate", type date}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([ToDate] - [FromDate]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Custom", each List.Dates([FromDate],[Subtraction],#duration(1,0,0,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the November 2023 Power BI update to learn about new features.