Working with Navision Archive and Version import

Hi Tolle502 ,

 

To get it in power query. Please check the following steps as below.

1. Group by the table in power query .

 

2.Expand the columns we need and filter the table.

 

 #"Filtered Rows" = Table.SelectRows(#"Expanded a", each ([a.Version No_] = [Count])),

 

 

3. Then we can get the excepted result.

 

The full code for your reference.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwMDBU0lECYwMDCAkESrE6CEkjsDBUiSmapDFIAVinEapOI7ixQMIUXQYsC5I2BRsYCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No_ = _t, #"Version No_" = _t, Quantity = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No_", Int64.Type}, {"Version No_", Int64.Type}, {"Quantity", Int64.Type}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"No_"}, {{"Count", each List.Max([Version No_]), type number}, {"a", each _, type table [No_=number, Version No_=number, Quantity=number, Amount=number]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Version No_", "Quantity", "Amount"}, {"a.Version No_", "a.Quantity", "a.Amount"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded a", each ([a.Version No_] = [Count])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"

 

 

Alternatively, we can make it by a calculated table.

 

Table 3 =
VAR k =
    ADDCOLUMNS (
        'table',
        "maxno", CALCULATE (
            MAX ( 'table'[Version No_] ),
            FILTER ( 'table', 'table'[No_] = EARLIER ( 'table'[No_] ) )
        )
    )
RETURN
    FILTER ( k, 'table'[Version No_] = [maxno] )

 

 

 

 

For more details, please chck the pbix as attached.