Forum Discussion

third_hicana's avatar
third_hicana
Helper IV
3 years ago
Solved

Creating a summary table without importing another source.

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 cre...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BeaBF's avatar
    BeaBF
    3 years ago

    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's avatar
third_hicana
Helper IV
3 years ago

BeaBF I applied the Source = #"Resource List",. However, anothe error appeared. 

 

 

BeaBF's avatar
BeaBF
Super User
3 years ago

third_hicana Let's recap.

Create a first table, named "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"

in this way:

 

Then, create a second table, named "Table" with this code:

let
Source = #"Resource List",
#"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"

in this way:

 

Follow the screenshots.

 

BBF

 

  • third_hicana's avatar
    third_hicana
    Helper IV
    3 years ago

    Hi BeaBF  Yay! Now it works. Thank you so much for your help and patience 🙂 . My last question would be does the 3 rows will automatically generate for next month and count the types? 🙂 Thanks again 

     

  • BeaBF's avatar
    BeaBF
    Super User
    3 years ago

    third_hicana Yes, you have to refresh the table using the appropriate button and it will automatically populate with the new month and the corresponding counts.

     

    BBF