New table with filtered distinct ID's

[Thomvdw]

Hi Community,

I have a datatable with multiple months below each other. The datatable consists of the column 'period' and 'ContractID'. 

The goal is to create a new table with only the distinct ContractID's from the last available month.

I tried multiple variations of lines of code, but none gave me the right answer. 

Hopefully somebody here can help.

Thanks, Thom

[Anonymous]

1. Sample, representative data for the problem.

2. Transformation description.

3. Desired outcome.

[Thomvdw]

Hi Daxer,

Sample datatable is:

Period ContractID
2021-01 1
2021-01 2
2021-01 3
2021-01 3
2021-02 2
2021-02 3
2021-02 3
2021-02 4

In this sample the last month is 2021-02, so I expect the new datatable outcome to be:

Period ContractID
2021-02 2
2021-02 3
2021-02 4

Hope this clarifies

[PaulOlding · Accepted solution]

Hi Thomvdw,

New Table =

VAR _MaxPeriod = MAX('Table'[Period])

RETURN

CALCULATETABLE(

SUMMARIZE('Table', 'Table'[Period], 'Table'[ContractID]),

'Table'[Period ] = _MaxPeriod

)

[Anonymous]

// Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MFQwVIrVQfCMUHjGOHlGKCqN0ORQeSZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Period ContractID" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Period ContractID", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Period ContractID.1", "Period ContractID.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period ContractID.1", type date}, {"Period ContractID.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Period ContractID.1", "Period"}, {"Period ContractID.2", "ContractID"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns"),
    #"Calculate Max Date" = List.Max(#"Removed Duplicates"[Period], null),
    #"Should Keep" = Table.AddColumn(
        #"Removed Duplicates", "Should Keep?",
        each [Period] = #"Calculate Max Date"
    ),
    #"Filtered Rows" = Table.SelectRows(#"Should Keep", each ([#"Should Keep?"] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Should Keep?"})
in
    #"Removed Columns"