Forum Discussion
Helper IVCreating 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
Super Userthird_hicana yes, it is possible. Can you paste some sample data of the table on which calculate the summary one? What does the summary table have to do?
BBF
Helper IVBeaBF 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.
- BeaBF
third_hicana here the code:
let
Source = Table,
#"Added Custom" = Table.AddColumn(Source, "Count", each if [Status] = "Ongoing" then 1 else 0),
#"Extracted Month Name" = Table.TransformColumns(#"Added Custom", {{"Start Date", each Date.MonthName(_), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Start Date", "Contract Type"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"Table in first line is your native table, from which create the summary one.
BBF
- third_hicana
Hi BeaBF This is awesome. A slight modification. Is there anyway that the count does not depend on the start date per se. So here are the rules
1. Every month (which is dependent on calendar and not on start date) should have 3 rows which are count of permanent, count of contractor, count of vacancy.
2. Vacancy should be counted repeatedly every calendar month if is still have no input in "Start Date"
3. Calendar should be automatic as time goes by. It is dependent to current month and automatically generate the 3 rows every time the calendar month starts.
This should be the table in query look like.
A bunch of thanks for your help.
- BeaBF
third_hicana Ok, first of all, create a new blank query as follow:
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, change the code of the prevoius one in:
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
- third_hicana
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.