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
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.
Super Userthird_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
BeaBF Apologies. When you say change the code, does it mean I will just add the second code from the first one in just one query.
What I did was, I created the first code then I edited it by erasing the first code and replace it by the second one. However, I got errors