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
third_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
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.
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
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.
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.
