Forum Discussion
Creating a summary table without importing another source.
third_hicana no sorry, with first one I refered to the first code i gave you about the new table.
Create the Table Type with this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC7JL1LSUTJWitWJVgpILcpNzEvNK4GLhCUmJ+YlV0L4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, DUMMY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}})
in
#"Changed Type"then a new table with:
let
Source = Table,
#"Added Custom1" = Table.AddColumn(Source, "DUMMY", each "3"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"DUMMY"}, Type, {"DUMMY"}, "Type", JoinKind.LeftOuter),
#"Expanded Type" = Table.ExpandTableColumn(#"Merged Queries", "Type", {"Type"}, {"Type.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Type", "Count", each if [Contract Type] = [Type.1] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Date", "Type.1"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"BBF
BeaBF Here's the sampe data.
| Resource Name | Contract Type | Skill Type | Maximum Capacity | Start Date | End Date | Status |
| Ana | Contractor | PM | 100% | 01/01/2022 | 04/11/2022 | |
| Bart | Permanent | SPM | 100% | 01/02/2022 | Ongoing | |
| Cathy | Permanent | SPM | 90% | 01/03/2022 | Ongoing | |
| Dennis | Permanent | SPM | 70% | 01/04/2022 | Ongoing | |
| Vacant | Vacancy | PRM | 100% | No Started | ||
| Edward | Contractor | PM | 50% | 01/04/2022 | Ongoing | |
| Faye | Contractor | PM | 30% | 01/03/2022 | Ongoing | |
| George | Contractor | PM | 30% | 01/04/2022 | Ongoing |
This is the query I want to achieve. It should count the number of each contract type per month without importing a table from excel. I put zero for Ana because her contract was already finished.
The summary table should count and segregate permanents, vacancies and contractors per month. So each month there should be 3 rows for permanent, vacancy and contractors.