Creating a summary table without importing another source.

[third_hicana]

Hi. I want to achieve this kind of visualisation. However. I don't know how to create a query that will be able to count the number of permanent, contractor, and vacancy per month. I just want to create this summary table from blank query and automatically update the query once another table is updated.

A kind of Table/Query that I want to achieve in PBI without importing an excel as data source. It summarizes the data below. The table should automatically count each contract type per month

Is this possible through DAX measure without importing a data source from excel? The Allocation should automatically contract type per month.

This is the data I have now which will be the basis for an automatic update of summary table above.

This is the visualisation I want to achieve. 

Thanks in advance for your help

[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

[BeaBF · Accepted solution]

@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]

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.